<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="generator" content="Asciidoctor 2.0.10">
<title>SparkSQL 练习项目 - 出租车利用率分析</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:300,300italic,400,400italic,600,600italic%7CNoto+Serif:400,400italic,700,700italic%7CDroid+Sans+Mono:400,700">
<style>
/* Asciidoctor default stylesheet | MIT License | https://asciidoctor.org */
/* Uncomment @import statement to use as custom stylesheet */
/*@import "https://fonts.googleapis.com/css?family=Open+Sans:300,300italic,400,400italic,600,600italic%7CNoto+Serif:400,400italic,700,700italic%7CDroid+Sans+Mono:400,700";*/
article,aside,details,figcaption,figure,footer,header,hgroup,main,nav,section{display:block}
audio,video{display:inline-block}
audio:not([controls]){display:none;height:0}
html{font-family:sans-serif;-ms-text-size-adjust:100%;-webkit-text-size-adjust:100%}
a{background:none}
a:focus{outline:thin dotted}
a:active,a:hover{outline:0}
h1{font-size:2em;margin:.67em 0}
abbr[title]{border-bottom:1px dotted}
b,strong{font-weight:bold}
dfn{font-style:italic}
hr{-moz-box-sizing:content-box;box-sizing:content-box;height:0}
mark{background:#ff0;color:#000}
code,kbd,pre,samp{font-family:monospace;font-size:1em}
pre{white-space:pre-wrap}
q{quotes:"\201C" "\201D" "\2018" "\2019"}
small{font-size:80%}
sub,sup{font-size:75%;line-height:0;position:relative;vertical-align:baseline}
sup{top:-.5em}
sub{bottom:-.25em}
img{border:0}
svg:not(:root){overflow:hidden}
figure{margin:0}
fieldset{border:1px solid silver;margin:0 2px;padding:.35em .625em .75em}
legend{border:0;padding:0}
button,input,select,textarea{font-family:inherit;font-size:100%;margin:0}
button,input{line-height:normal}
button,select{text-transform:none}
button,html input[type="button"],input[type="reset"],input[type="submit"]{-webkit-appearance:button;cursor:pointer}
button[disabled],html input[disabled]{cursor:default}
input[type="checkbox"],input[type="radio"]{box-sizing:border-box;padding:0}
button::-moz-focus-inner,input::-moz-focus-inner{border:0;padding:0}
textarea{overflow:auto;vertical-align:top}
table{border-collapse:collapse;border-spacing:0}
*,*::before,*::after{-moz-box-sizing:border-box;-webkit-box-sizing:border-box;box-sizing:border-box}
html,body{font-size:100%}
body{background:#fff;color:rgba(0,0,0,.8);padding:0;margin:0;font-family:"Noto Serif","DejaVu Serif",serif;font-weight:400;font-style:normal;line-height:1;position:relative;cursor:auto;tab-size:4;-moz-osx-font-smoothing:grayscale;-webkit-font-smoothing:antialiased}
a:hover{cursor:pointer}
img,object,embed{max-width:100%;height:auto}
object,embed{height:100%}
img{-ms-interpolation-mode:bicubic}
.left{float:left!important}
.right{float:right!important}
.text-left{text-align:left!important}
.text-right{text-align:right!important}
.text-center{text-align:center!important}
.text-justify{text-align:justify!important}
.hide{display:none}
img,object,svg{display:inline-block;vertical-align:middle}
textarea{height:auto;min-height:50px}
select{width:100%}
.center{margin-left:auto;margin-right:auto}
.stretch{width:100%}
.subheader,.admonitionblock td.content>.title,.audioblock>.title,.exampleblock>.title,.imageblock>.title,.listingblock>.title,.literalblock>.title,.stemblock>.title,.openblock>.title,.paragraph>.title,.quoteblock>.title,table.tableblock>.title,.verseblock>.title,.videoblock>.title,.dlist>.title,.olist>.title,.ulist>.title,.qlist>.title,.hdlist>.title{line-height:1.45;color:#7a2518;font-weight:400;margin-top:0;margin-bottom:.25em}
div,dl,dt,dd,ul,ol,li,h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6,pre,form,p,blockquote,th,td{margin:0;padding:0;direction:ltr}
a{color:#2156a5;text-decoration:underline;line-height:inherit}
a:hover,a:focus{color:#1d4b8f}
a img{border:0}
p{font-family:inherit;font-weight:400;font-size:1em;line-height:1.6;margin-bottom:1.25em;text-rendering:optimizeLegibility}
p aside{font-size:.875em;line-height:1.35;font-style:italic}
h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{font-family:"Open Sans","DejaVu Sans",sans-serif;font-weight:300;font-style:normal;color:#ba3925;text-rendering:optimizeLegibility;margin-top:1em;margin-bottom:.5em;line-height:1.0125em}
h1 small,h2 small,h3 small,#toctitle small,.sidebarblock>.content>.title small,h4 small,h5 small,h6 small{font-size:60%;color:#e99b8f;line-height:0}
h1{font-size:2.125em}
h2{font-size:1.6875em}
h3,#toctitle,.sidebarblock>.content>.title{font-size:1.375em}
h4,h5{font-size:1.125em}
h6{font-size:1em}
hr{border:solid #dddddf;border-width:1px 0 0;clear:both;margin:1.25em 0 1.1875em;height:0}
em,i{font-style:italic;line-height:inherit}
strong,b{font-weight:bold;line-height:inherit}
small{font-size:60%;line-height:inherit}
code{font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;font-weight:400;color:rgba(0,0,0,.9)}
ul,ol,dl{font-size:1em;line-height:1.6;margin-bottom:1.25em;list-style-position:outside;font-family:inherit}
ul,ol{margin-left:1.5em}
ul li ul,ul li ol{margin-left:1.25em;margin-bottom:0;font-size:1em}
ul.square li ul,ul.circle li ul,ul.disc li ul{list-style:inherit}
ul.square{list-style-type:square}
ul.circle{list-style-type:circle}
ul.disc{list-style-type:disc}
ol li ul,ol li ol{margin-left:1.25em;margin-bottom:0}
dl dt{margin-bottom:.3125em;font-weight:bold}
dl dd{margin-bottom:1.25em}
abbr,acronym{text-transform:uppercase;font-size:90%;color:rgba(0,0,0,.8);border-bottom:1px dotted #ddd;cursor:help}
abbr{text-transform:none}
blockquote{margin:0 0 1.25em;padding:.5625em 1.25em 0 1.1875em;border-left:1px solid #ddd}
blockquote cite{display:block;font-size:.9375em;color:rgba(0,0,0,.6)}
blockquote cite::before{content:"\2014 \0020"}
blockquote cite a,blockquote cite a:visited{color:rgba(0,0,0,.6)}
blockquote,blockquote p{line-height:1.6;color:rgba(0,0,0,.85)}
@media screen and (min-width:768px){h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{line-height:1.2}
h1{font-size:2.75em}
h2{font-size:2.3125em}
h3,#toctitle,.sidebarblock>.content>.title{font-size:1.6875em}
h4{font-size:1.4375em}}
table{background:#fff;margin-bottom:1.25em;border:solid 1px #dedede}
table thead,table tfoot{background:#f7f8f7}
table thead tr th,table thead tr td,table tfoot tr th,table tfoot tr td{padding:.5em .625em .625em;font-size:inherit;color:rgba(0,0,0,.8);text-align:left}
table tr th,table tr td{padding:.5625em .625em;font-size:inherit;color:rgba(0,0,0,.8)}
table tr.even,table tr.alt{background:#f8f8f7}
table thead tr th,table tfoot tr th,table tbody tr td,table tr td,table tfoot tr td{display:table-cell;line-height:1.6}
h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{line-height:1.2;word-spacing:-.05em}
h1 strong,h2 strong,h3 strong,#toctitle strong,.sidebarblock>.content>.title strong,h4 strong,h5 strong,h6 strong{font-weight:400}
.clearfix::before,.clearfix::after,.float-group::before,.float-group::after{content:" ";display:table}
.clearfix::after,.float-group::after{clear:both}
:not(pre):not([class^=L])>code{font-size:.9375em;font-style:normal!important;letter-spacing:0;padding:.1em .5ex;word-spacing:-.15em;background:#f7f7f8;-webkit-border-radius:4px;border-radius:4px;line-height:1.45;text-rendering:optimizeSpeed;word-wrap:break-word}
:not(pre)>code.nobreak{word-wrap:normal}
:not(pre)>code.nowrap{white-space:nowrap}
pre{color:rgba(0,0,0,.9);font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;line-height:1.45;text-rendering:optimizeSpeed}
pre code,pre pre{color:inherit;font-size:inherit;line-height:inherit}
pre>code{display:block}
pre.nowrap,pre.nowrap pre{white-space:pre;word-wrap:normal}
em em{font-style:normal}
strong strong{font-weight:400}
.keyseq{color:rgba(51,51,51,.8)}
kbd{font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;display:inline-block;color:rgba(0,0,0,.8);font-size:.65em;line-height:1.45;background:#f7f7f7;border:1px solid #ccc;-webkit-border-radius:3px;border-radius:3px;-webkit-box-shadow:0 1px 0 rgba(0,0,0,.2),0 0 0 .1em white inset;box-shadow:0 1px 0 rgba(0,0,0,.2),0 0 0 .1em #fff inset;margin:0 .15em;padding:.2em .5em;vertical-align:middle;position:relative;top:-.1em;white-space:nowrap}
.keyseq kbd:first-child{margin-left:0}
.keyseq kbd:last-child{margin-right:0}
.menuseq,.menuref{color:#000}
.menuseq b:not(.caret),.menuref{font-weight:inherit}
.menuseq{word-spacing:-.02em}
.menuseq b.caret{font-size:1.25em;line-height:.8}
.menuseq i.caret{font-weight:bold;text-align:center;width:.45em}
b.button::before,b.button::after{position:relative;top:-1px;font-weight:400}
b.button::before{content:"[";padding:0 3px 0 2px}
b.button::after{content:"]";padding:0 2px 0 3px}
p a>code:hover{color:rgba(0,0,0,.9)}
#header,#content,#footnotes,#footer{width:100%;margin-left:auto;margin-right:auto;margin-top:0;margin-bottom:0;max-width:62.5em;*zoom:1;position:relative;padding-left:.9375em;padding-right:.9375em}
#header::before,#header::after,#content::before,#content::after,#footnotes::before,#footnotes::after,#footer::before,#footer::after{content:" ";display:table}
#header::after,#content::after,#footnotes::after,#footer::after{clear:both}
#content{margin-top:1.25em}
#content::before{content:none}
#header>h1:first-child{color:rgba(0,0,0,.85);margin-top:2.25rem;margin-bottom:0}
#header>h1:first-child+#toc{margin-top:8px;border-top:1px solid #dddddf}
#header>h1:only-child,body.toc2 #header>h1:nth-last-child(2){border-bottom:1px solid #dddddf;padding-bottom:8px}
#header .details{border-bottom:1px solid #dddddf;line-height:1.45;padding-top:.25em;padding-bottom:.25em;padding-left:.25em;color:rgba(0,0,0,.6);display:-ms-flexbox;display:-webkit-flex;display:flex;-ms-flex-flow:row wrap;-webkit-flex-flow:row wrap;flex-flow:row wrap}
#header .details span:first-child{margin-left:-.125em}
#header .details span.email a{color:rgba(0,0,0,.85)}
#header .details br{display:none}
#header .details br+span::before{content:"\00a0\2013\00a0"}
#header .details br+span.author::before{content:"\00a0\22c5\00a0";color:rgba(0,0,0,.85)}
#header .details br+span#revremark::before{content:"\00a0|\00a0"}
#header #revnumber{text-transform:capitalize}
#header #revnumber::after{content:"\00a0"}
#content>h1:first-child:not([class]){color:rgba(0,0,0,.85);border-bottom:1px solid #dddddf;padding-bottom:8px;margin-top:0;padding-top:1rem;margin-bottom:1.25rem}
#toc{border-bottom:1px solid #e7e7e9;padding-bottom:.5em}
#toc>ul{margin-left:.125em}
#toc ul.sectlevel0>li>a{font-style:italic}
#toc ul.sectlevel0 ul.sectlevel1{margin:.5em 0}
#toc ul{font-family:"Open Sans","DejaVu Sans",sans-serif;list-style-type:none}
#toc li{line-height:1.3334;margin-top:.3334em}
#toc a{text-decoration:none}
#toc a:active{text-decoration:underline}
#toctitle{color:#7a2518;font-size:1.2em}
@media screen and (min-width:768px){#toctitle{font-size:1.375em}
body.toc2{padding-left:15em;padding-right:0}
#toc.toc2{margin-top:0!important;background:#f8f8f7;position:fixed;width:15em;left:0;top:0;border-right:1px solid #e7e7e9;border-top-width:0!important;border-bottom-width:0!important;z-index:1000;padding:1.25em 1em;height:100%;overflow:auto}
#toc.toc2 #toctitle{margin-top:0;margin-bottom:.8rem;font-size:1.2em}
#toc.toc2>ul{font-size:.9em;margin-bottom:0}
#toc.toc2 ul ul{margin-left:0;padding-left:1em}
#toc.toc2 ul.sectlevel0 ul.sectlevel1{padding-left:0;margin-top:.5em;margin-bottom:.5em}
body.toc2.toc-right{padding-left:0;padding-right:15em}
body.toc2.toc-right #toc.toc2{border-right-width:0;border-left:1px solid #e7e7e9;left:auto;right:0}}
@media screen and (min-width:1280px){body.toc2{padding-left:20em;padding-right:0}
#toc.toc2{width:20em}
#toc.toc2 #toctitle{font-size:1.375em}
#toc.toc2>ul{font-size:.95em}
#toc.toc2 ul ul{padding-left:1.25em}
body.toc2.toc-right{padding-left:0;padding-right:20em}}
#content #toc{border-style:solid;border-width:1px;border-color:#e0e0dc;margin-bottom:1.25em;padding:1.25em;background:#f8f8f7;-webkit-border-radius:4px;border-radius:4px}
#content #toc>:first-child{margin-top:0}
#content #toc>:last-child{margin-bottom:0}
#footer{max-width:100%;background:rgba(0,0,0,.8);padding:1.25em}
#footer-text{color:rgba(255,255,255,.8);line-height:1.44}
#content{margin-bottom:.625em}
.sect1{padding-bottom:.625em}
@media screen and (min-width:768px){#content{margin-bottom:1.25em}
.sect1{padding-bottom:1.25em}}
.sect1:last-child{padding-bottom:0}
.sect1+.sect1{border-top:1px solid #e7e7e9}
#content h1>a.anchor,h2>a.anchor,h3>a.anchor,#toctitle>a.anchor,.sidebarblock>.content>.title>a.anchor,h4>a.anchor,h5>a.anchor,h6>a.anchor{position:absolute;z-index:1001;width:1.5ex;margin-left:-1.5ex;display:block;text-decoration:none!important;visibility:hidden;text-align:center;font-weight:400}
#content h1>a.anchor::before,h2>a.anchor::before,h3>a.anchor::before,#toctitle>a.anchor::before,.sidebarblock>.content>.title>a.anchor::before,h4>a.anchor::before,h5>a.anchor::before,h6>a.anchor::before{content:"\00A7";font-size:.85em;display:block;padding-top:.1em}
#content h1:hover>a.anchor,#content h1>a.anchor:hover,h2:hover>a.anchor,h2>a.anchor:hover,h3:hover>a.anchor,#toctitle:hover>a.anchor,.sidebarblock>.content>.title:hover>a.anchor,h3>a.anchor:hover,#toctitle>a.anchor:hover,.sidebarblock>.content>.title>a.anchor:hover,h4:hover>a.anchor,h4>a.anchor:hover,h5:hover>a.anchor,h5>a.anchor:hover,h6:hover>a.anchor,h6>a.anchor:hover{visibility:visible}
#content h1>a.link,h2>a.link,h3>a.link,#toctitle>a.link,.sidebarblock>.content>.title>a.link,h4>a.link,h5>a.link,h6>a.link{color:#ba3925;text-decoration:none}
#content h1>a.link:hover,h2>a.link:hover,h3>a.link:hover,#toctitle>a.link:hover,.sidebarblock>.content>.title>a.link:hover,h4>a.link:hover,h5>a.link:hover,h6>a.link:hover{color:#a53221}
details,.audioblock,.imageblock,.literalblock,.listingblock,.stemblock,.videoblock{margin-bottom:1.25em}
details>summary:first-of-type{cursor:pointer;display:list-item;outline:none;margin-bottom:.75em}
.admonitionblock td.content>.title,.audioblock>.title,.exampleblock>.title,.imageblock>.title,.listingblock>.title,.literalblock>.title,.stemblock>.title,.openblock>.title,.paragraph>.title,.quoteblock>.title,table.tableblock>.title,.verseblock>.title,.videoblock>.title,.dlist>.title,.olist>.title,.ulist>.title,.qlist>.title,.hdlist>.title{text-rendering:optimizeLegibility;text-align:left;font-family:"Noto Serif","DejaVu Serif",serif;font-size:1rem;font-style:italic}
table.tableblock.fit-content>caption.title{white-space:nowrap;width:0}
.paragraph.lead>p,#preamble>.sectionbody>[class="paragraph"]:first-of-type p{font-size:1.21875em;line-height:1.6;color:rgba(0,0,0,.85)}
table.tableblock #preamble>.sectionbody>[class="paragraph"]:first-of-type p{font-size:inherit}
.admonitionblock>table{border-collapse:separate;border:0;background:none;width:100%}
.admonitionblock>table td.icon{text-align:center;width:80px}
.admonitionblock>table td.icon img{max-width:none}
.admonitionblock>table td.icon .title{font-weight:bold;font-family:"Open Sans","DejaVu Sans",sans-serif;text-transform:uppercase}
.admonitionblock>table td.content{padding-left:1.125em;padding-right:1.25em;border-left:1px solid #dddddf;color:rgba(0,0,0,.6)}
.admonitionblock>table td.content>:last-child>:last-child{margin-bottom:0}
.exampleblock>.content{border-style:solid;border-width:1px;border-color:#e6e6e6;margin-bottom:1.25em;padding:1.25em;background:#fff;-webkit-border-radius:4px;border-radius:4px}
.exampleblock>.content>:first-child{margin-top:0}
.exampleblock>.content>:last-child{margin-bottom:0}
.sidebarblock{border-style:solid;border-width:1px;border-color:#dbdbd6;margin-bottom:1.25em;padding:1.25em;background:#f3f3f2;-webkit-border-radius:4px;border-radius:4px}
.sidebarblock>:first-child{margin-top:0}
.sidebarblock>:last-child{margin-bottom:0}
.sidebarblock>.content>.title{color:#7a2518;margin-top:0;text-align:center}
.exampleblock>.content>:last-child>:last-child,.exampleblock>.content .olist>ol>li:last-child>:last-child,.exampleblock>.content .ulist>ul>li:last-child>:last-child,.exampleblock>.content .qlist>ol>li:last-child>:last-child,.sidebarblock>.content>:last-child>:last-child,.sidebarblock>.content .olist>ol>li:last-child>:last-child,.sidebarblock>.content .ulist>ul>li:last-child>:last-child,.sidebarblock>.content .qlist>ol>li:last-child>:last-child{margin-bottom:0}
.literalblock pre,.listingblock>.content>pre{-webkit-border-radius:4px;border-radius:4px;word-wrap:break-word;overflow-x:auto;padding:1em;font-size:.8125em}
@media screen and (min-width:768px){.literalblock pre,.listingblock>.content>pre{font-size:.90625em}}
@media screen and (min-width:1280px){.literalblock pre,.listingblock>.content>pre{font-size:1em}}
.literalblock pre,.listingblock>.content>pre:not(.highlight),.listingblock>.content>pre[class="highlight"],.listingblock>.content>pre[class^="highlight "]{background:#f7f7f8}
.literalblock.output pre{color:#f7f7f8;background:rgba(0,0,0,.9)}
.listingblock>.content{position:relative}
.listingblock code[data-lang]::before{display:none;content:attr(data-lang);position:absolute;font-size:.75em;top:.425rem;right:.5rem;line-height:1;text-transform:uppercase;color:inherit;opacity:.5}
.listingblock:hover code[data-lang]::before{display:block}
.listingblock.terminal pre .command::before{content:attr(data-prompt);padding-right:.5em;color:inherit;opacity:.5}
.listingblock.terminal pre .command:not([data-prompt])::before{content:"$"}
.listingblock pre.highlightjs{padding:0}
.listingblock pre.highlightjs>code{padding:1em;-webkit-border-radius:4px;border-radius:4px}
.listingblock pre.prettyprint{border-width:0}
.prettyprint{background:#f7f7f8}
pre.prettyprint .linenums{line-height:1.45;margin-left:2em}
pre.prettyprint li{background:none;list-style-type:inherit;padding-left:0}
pre.prettyprint li code[data-lang]::before{opacity:1}
pre.prettyprint li:not(:first-child) code[data-lang]::before{display:none}
table.linenotable{border-collapse:separate;border:0;margin-bottom:0;background:none}
table.linenotable td[class]{color:inherit;vertical-align:top;padding:0;line-height:inherit;white-space:normal}
table.linenotable td.code{padding-left:.75em}
table.linenotable td.linenos{border-right:1px solid currentColor;opacity:.35;padding-right:.5em}
pre.pygments .lineno{border-right:1px solid currentColor;opacity:.35;display:inline-block;margin-right:.75em}
pre.pygments .lineno::before{content:"";margin-right:-.125em}
.quoteblock{margin:0 1em 1.25em 1.5em;display:table}
.quoteblock:not(.excerpt)>.title{margin-left:-1.5em;margin-bottom:.75em}
.quoteblock blockquote,.quoteblock p{color:rgba(0,0,0,.85);font-size:1.15rem;line-height:1.75;word-spacing:.1em;letter-spacing:0;font-style:italic;text-align:justify}
.quoteblock blockquote{margin:0;padding:0;border:0}
.quoteblock blockquote::before{content:"\201c";float:left;font-size:2.75em;font-weight:bold;line-height:.6em;margin-left:-.6em;color:#7a2518;text-shadow:0 1px 2px rgba(0,0,0,.1)}
.quoteblock blockquote>.paragraph:last-child p{margin-bottom:0}
.quoteblock .attribution{margin-top:.75em;margin-right:.5ex;text-align:right}
.verseblock{margin:0 1em 1.25em}
.verseblock pre{font-family:"Open Sans","DejaVu Sans",sans;font-size:1.15rem;color:rgba(0,0,0,.85);font-weight:300;text-rendering:optimizeLegibility}
.verseblock pre strong{font-weight:400}
.verseblock .attribution{margin-top:1.25rem;margin-left:.5ex}
.quoteblock .attribution,.verseblock .attribution{font-size:.9375em;line-height:1.45;font-style:italic}
.quoteblock .attribution br,.verseblock .attribution br{display:none}
.quoteblock .attribution cite,.verseblock .attribution cite{display:block;letter-spacing:-.025em;color:rgba(0,0,0,.6)}
.quoteblock.abstract blockquote::before,.quoteblock.excerpt blockquote::before,.quoteblock .quoteblock blockquote::before{display:none}
.quoteblock.abstract blockquote,.quoteblock.abstract p,.quoteblock.excerpt blockquote,.quoteblock.excerpt p,.quoteblock .quoteblock blockquote,.quoteblock .quoteblock p{line-height:1.6;word-spacing:0}
.quoteblock.abstract{margin:0 1em 1.25em;display:block}
.quoteblock.abstract>.title{margin:0 0 .375em;font-size:1.15em;text-align:center}
.quoteblock.excerpt>blockquote,.quoteblock .quoteblock{padding:0 0 .25em 1em;border-left:.25em solid #dddddf}
.quoteblock.excerpt,.quoteblock .quoteblock{margin-left:0}
.quoteblock.excerpt blockquote,.quoteblock.excerpt p,.quoteblock .quoteblock blockquote,.quoteblock .quoteblock p{color:inherit;font-size:1.0625rem}
.quoteblock.excerpt .attribution,.quoteblock .quoteblock .attribution{color:inherit;text-align:left;margin-right:0}
table.tableblock{max-width:100%;border-collapse:separate}
p.tableblock:last-child{margin-bottom:0}
td.tableblock>.content>:last-child{margin-bottom:-1.25em}
td.tableblock>.content>:last-child.sidebarblock{margin-bottom:0}
table.tableblock,th.tableblock,td.tableblock{border:0 solid #dedede}
table.grid-all>thead>tr>.tableblock,table.grid-all>tbody>tr>.tableblock{border-width:0 1px 1px 0}
table.grid-all>tfoot>tr>.tableblock{border-width:1px 1px 0 0}
table.grid-cols>*>tr>.tableblock{border-width:0 1px 0 0}
table.grid-rows>thead>tr>.tableblock,table.grid-rows>tbody>tr>.tableblock{border-width:0 0 1px}
table.grid-rows>tfoot>tr>.tableblock{border-width:1px 0 0}
table.grid-all>*>tr>.tableblock:last-child,table.grid-cols>*>tr>.tableblock:last-child{border-right-width:0}
table.grid-all>tbody>tr:last-child>.tableblock,table.grid-all>thead:last-child>tr>.tableblock,table.grid-rows>tbody>tr:last-child>.tableblock,table.grid-rows>thead:last-child>tr>.tableblock{border-bottom-width:0}
table.frame-all{border-width:1px}
table.frame-sides{border-width:0 1px}
table.frame-topbot,table.frame-ends{border-width:1px 0}
table.stripes-all tr,table.stripes-odd tr:nth-of-type(odd),table.stripes-even tr:nth-of-type(even),table.stripes-hover tr:hover{background:#f8f8f7}
th.halign-left,td.halign-left{text-align:left}
th.halign-right,td.halign-right{text-align:right}
th.halign-center,td.halign-center{text-align:center}
th.valign-top,td.valign-top{vertical-align:top}
th.valign-bottom,td.valign-bottom{vertical-align:bottom}
th.valign-middle,td.valign-middle{vertical-align:middle}
table thead th,table tfoot th{font-weight:bold}
tbody tr th{display:table-cell;line-height:1.6;background:#f7f8f7}
tbody tr th,tbody tr th p,tfoot tr th,tfoot tr th p{color:rgba(0,0,0,.8);font-weight:bold}
p.tableblock>code:only-child{background:none;padding:0}
p.tableblock{font-size:1em}
ol{margin-left:1.75em}
ul li ol{margin-left:1.5em}
dl dd{margin-left:1.125em}
dl dd:last-child,dl dd:last-child>:last-child{margin-bottom:0}
ol>li p,ul>li p,ul dd,ol dd,.olist .olist,.ulist .ulist,.ulist .olist,.olist .ulist{margin-bottom:.625em}
ul.checklist,ul.none,ol.none,ul.no-bullet,ol.no-bullet,ol.unnumbered,ul.unstyled,ol.unstyled{list-style-type:none}
ul.no-bullet,ol.no-bullet,ol.unnumbered{margin-left:.625em}
ul.unstyled,ol.unstyled{margin-left:0}
ul.checklist{margin-left:.625em}
ul.checklist li>p:first-child>.fa-square-o:first-child,ul.checklist li>p:first-child>.fa-check-square-o:first-child{width:1.25em;font-size:.8em;position:relative;bottom:.125em}
ul.checklist li>p:first-child>input[type="checkbox"]:first-child{margin-right:.25em}
ul.inline{display:-ms-flexbox;display:-webkit-box;display:flex;-ms-flex-flow:row wrap;-webkit-flex-flow:row wrap;flex-flow:row wrap;list-style:none;margin:0 0 .625em -1.25em}
ul.inline>li{margin-left:1.25em}
.unstyled dl dt{font-weight:400;font-style:normal}
ol.arabic{list-style-type:decimal}
ol.decimal{list-style-type:decimal-leading-zero}
ol.loweralpha{list-style-type:lower-alpha}
ol.upperalpha{list-style-type:upper-alpha}
ol.lowerroman{list-style-type:lower-roman}
ol.upperroman{list-style-type:upper-roman}
ol.lowergreek{list-style-type:lower-greek}
.hdlist>table,.colist>table{border:0;background:none}
.hdlist>table>tbody>tr,.colist>table>tbody>tr{background:none}
td.hdlist1,td.hdlist2{vertical-align:top;padding:0 .625em}
td.hdlist1{font-weight:bold;padding-bottom:1.25em}
.literalblock+.colist,.listingblock+.colist{margin-top:-.5em}
.colist td:not([class]):first-child{padding:.4em .75em 0;line-height:1;vertical-align:top}
.colist td:not([class]):first-child img{max-width:none}
.colist td:not([class]):last-child{padding:.25em 0}
.thumb,.th{line-height:0;display:inline-block;border:solid 4px #fff;-webkit-box-shadow:0 0 0 1px #ddd;box-shadow:0 0 0 1px #ddd}
.imageblock.left{margin:.25em .625em 1.25em 0}
.imageblock.right{margin:.25em 0 1.25em .625em}
.imageblock>.title{margin-bottom:0}
.imageblock.thumb,.imageblock.th{border-width:6px}
.imageblock.thumb>.title,.imageblock.th>.title{padding:0 .125em}
.image.left,.image.right{margin-top:.25em;margin-bottom:.25em;display:inline-block;line-height:0}
.image.left{margin-right:.625em}
.image.right{margin-left:.625em}
a.image{text-decoration:none;display:inline-block}
a.image object{pointer-events:none}
sup.footnote,sup.footnoteref{font-size:.875em;position:static;vertical-align:super}
sup.footnote a,sup.footnoteref a{text-decoration:none}
sup.footnote a:active,sup.footnoteref a:active{text-decoration:underline}
#footnotes{padding-top:.75em;padding-bottom:.75em;margin-bottom:.625em}
#footnotes hr{width:20%;min-width:6.25em;margin:-.25em 0 .75em;border-width:1px 0 0}
#footnotes .footnote{padding:0 .375em 0 .225em;line-height:1.3334;font-size:.875em;margin-left:1.2em;margin-bottom:.2em}
#footnotes .footnote a:first-of-type{font-weight:bold;text-decoration:none;margin-left:-1.05em}
#footnotes .footnote:last-of-type{margin-bottom:0}
#content #footnotes{margin-top:-.625em;margin-bottom:0;padding:.75em 0}
.gist .file-data>table{border:0;background:#fff;width:100%;margin-bottom:0}
.gist .file-data>table td.line-data{width:99%}
div.unbreakable{page-break-inside:avoid}
.big{font-size:larger}
.small{font-size:smaller}
.underline{text-decoration:underline}
.overline{text-decoration:overline}
.line-through{text-decoration:line-through}
.aqua{color:#00bfbf}
.aqua-background{background:#00fafa}
.black{color:#000}
.black-background{background:#000}
.blue{color:#0000bf}
.blue-background{background:#0000fa}
.fuchsia{color:#bf00bf}
.fuchsia-background{background:#fa00fa}
.gray{color:#606060}
.gray-background{background:#7d7d7d}
.green{color:#006000}
.green-background{background:#007d00}
.lime{color:#00bf00}
.lime-background{background:#00fa00}
.maroon{color:#600000}
.maroon-background{background:#7d0000}
.navy{color:#000060}
.navy-background{background:#00007d}
.olive{color:#606000}
.olive-background{background:#7d7d00}
.purple{color:#600060}
.purple-background{background:#7d007d}
.red{color:#bf0000}
.red-background{background:#fa0000}
.silver{color:#909090}
.silver-background{background:#bcbcbc}
.teal{color:#006060}
.teal-background{background:#007d7d}
.white{color:#bfbfbf}
.white-background{background:#fafafa}
.yellow{color:#bfbf00}
.yellow-background{background:#fafa00}
span.icon>.fa{cursor:default}
a span.icon>.fa{cursor:inherit}
.admonitionblock td.icon [class^="fa icon-"]{font-size:2.5em;text-shadow:1px 1px 2px rgba(0,0,0,.5);cursor:default}
.admonitionblock td.icon .icon-note::before{content:"\f05a";color:#19407c}
.admonitionblock td.icon .icon-tip::before{content:"\f0eb";text-shadow:1px 1px 2px rgba(155,155,0,.8);color:#111}
.admonitionblock td.icon .icon-warning::before{content:"\f071";color:#bf6900}
.admonitionblock td.icon .icon-caution::before{content:"\f06d";color:#bf3400}
.admonitionblock td.icon .icon-important::before{content:"\f06a";color:#bf0000}
.conum[data-value]{display:inline-block;color:#fff!important;background:rgba(0,0,0,.8);-webkit-border-radius:100px;border-radius:100px;text-align:center;font-size:.75em;width:1.67em;height:1.67em;line-height:1.67em;font-family:"Open Sans","DejaVu Sans",sans-serif;font-style:normal;font-weight:bold}
.conum[data-value] *{color:#fff!important}
.conum[data-value]+b{display:none}
.conum[data-value]::after{content:attr(data-value)}
pre .conum[data-value]{position:relative;top:-.125em}
b.conum *{color:inherit!important}
.conum:not([data-value]):empty{display:none}
dt,th.tableblock,td.content,div.footnote{text-rendering:optimizeLegibility}
h1,h2,p,td.content,span.alt{letter-spacing:-.01em}
p strong,td.content strong,div.footnote strong{letter-spacing:-.005em}
p,blockquote,dt,td.content,span.alt{font-size:1.0625rem}
p{margin-bottom:1.25rem}
.sidebarblock p,.sidebarblock dt,.sidebarblock td.content,p.tableblock{font-size:1em}
.exampleblock>.content{background:#fffef7;border-color:#e0e0dc;-webkit-box-shadow:0 1px 4px #e0e0dc;box-shadow:0 1px 4px #e0e0dc}
.print-only{display:none!important}
@page{margin:1.25cm .75cm}
@media print{*{-webkit-box-shadow:none!important;box-shadow:none!important;text-shadow:none!important}
html{font-size:80%}
a{color:inherit!important;text-decoration:underline!important}
a.bare,a[href^="#"],a[href^="mailto:"]{text-decoration:none!important}
a[href^="http:"]:not(.bare)::after,a[href^="https:"]:not(.bare)::after{content:"(" attr(href) ")";display:inline-block;font-size:.875em;padding-left:.25em}
abbr[title]::after{content:" (" attr(title) ")"}
pre,blockquote,tr,img,object,svg{page-break-inside:avoid}
thead{display:table-header-group}
svg{max-width:100%}
p,blockquote,dt,td.content{font-size:1em;orphans:3;widows:3}
h2,h3,#toctitle,.sidebarblock>.content>.title{page-break-after:avoid}
#toc,.sidebarblock,.exampleblock>.content{background:none!important}
#toc{border-bottom:1px solid #dddddf!important;padding-bottom:0!important}
body.book #header{text-align:center}
body.book #header>h1:first-child{border:0!important;margin:2.5em 0 1em}
body.book #header .details{border:0!important;display:block;padding:0!important}
body.book #header .details span:first-child{margin-left:0!important}
body.book #header .details br{display:block}
body.book #header .details br+span::before{content:none!important}
body.book #toc{border:0!important;text-align:left!important;padding:0!important;margin:0!important}
body.book #toc,body.book #preamble,body.book h1.sect0,body.book .sect1>h2{page-break-before:always}
.listingblock code[data-lang]::before{display:block}
#footer{padding:0 .9375em}
.hide-on-print{display:none!important}
.print-only{display:block!important}
.hide-for-print{display:none!important}
.show-for-print{display:inherit!important}}
@media print,amzn-kf8{#header>h1:first-child{margin-top:1.25rem}
.sect1{padding:0!important}
.sect1+.sect1{border:0}
#footer{background:none}
#footer-text{color:rgba(0,0,0,.6);font-size:.9em}}
@media amzn-kf8{#header,#content,#footnotes,#footer{padding:0}}
</style>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body class="article toc2 toc-left">
<div id="header">
<h1>SparkSQL 练习项目 - 出租车利用率分析</h1>
<div id="toc" class="toc2">
<div id="toctitle">Table of Contents</div>
<ul class="sectlevel1">
<li><a href="#_1_业务">1. 业务</a></li>
<li><a href="#_2_流程分析">2. 流程分析</a></li>
<li><a href="#_3_数据读取">3. 数据读取</a></li>
<li><a href="#_5_数据清洗">5. 数据清洗</a></li>
<li><a href="#_6_行政区信息">6. 行政区信息</a>
<ul class="sectlevel2">
<li><a href="#_6_1_需求介绍">6.1. 需求介绍</a></li>
<li><a href="#_6_2_工具介绍">6.2. 工具介绍</a></li>
<li><a href="#_6_3_具体实现">6.3. 具体实现</a></li>
</ul>
</li>
<li><a href="#_7_会话统计">7. 会话统计</a></li>
</ul>
</div>
</div>
<div id="content">
<div id="preamble">
<div class="sectionbody">
<div class="exampleblock">
<div class="title">导读</div>
<div class="content">
<div class="paragraph">
<p>本项目是 SparkSQL 阶段的练习项目, 主要目的是夯实同学们对于 SparkSQL 的理解和使用</p>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1">数据集</dt>
<dd>
<div class="paragraph">
<p>2013年纽约市出租车乘车记录</p>
</div>
</dd>
<dt class="hdlist1">需求</dt>
<dd>
<div class="paragraph">
<p>统计出租车利用率, 到某个目的地后, 出租车等待下一个客人的间隔</p>
</div>
</dd>
</dl>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_1_业务">1. 业务</h2>
<div class="sectionbody">
<div class="exampleblock">
<div class="title">导读</div>
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>数据集介绍</p>
</li>
<li>
<p>业务场景介绍</p>
</li>
<li>
<p>和其它业务的关联</p>
</li>
<li>
<p>通过项目能学到什么</p>
</li>
</ol>
</div>
</div>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1">数据集结构</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 33.3333%;">
<col style="width: 33.3333%;">
<col style="width: 33.3334%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">字段</th>
<th class="tableblock halign-left valign-top">示例</th>
<th class="tableblock halign-left valign-top">示意</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>hack_license</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>BA96DE419E711691B9445D6A6307C170</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">执照号, 可以唯一标识一辆出租车</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>pickup_datetime</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>2013-01-01 15:11:48</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">上车时间</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>dropoff_datetime</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>2013-01-01 15:18:10</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">下车时间</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>pickup_longitude</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>-73.978165</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">上车点</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>pickup_latitude</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>40.757977</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">上车点</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>dropoff_longitude</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>-73.989838</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">下车点</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>dropoff_latitude</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>40.751171</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">下车点</p></td>
</tr>
</tbody>
</table>
<div class="paragraph">
<p>其中有三个点需要注意</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>hack_license</code> 是出租车执照, 可以唯一标识一辆出租车</p>
</li>
<li>
<p><code>pickup_datetime</code> 和 <code>dropoff_datetime</code> 分别是上车时间和下车时间, 通过这个时间, 可以获知行车时间</p>
</li>
<li>
<p><code>pickup_longitude</code> 和 <code>dropoff_longitude</code> 是经度, 经度所代表的是横轴, 也就是 X 轴</p>
</li>
<li>
<p><code>pickup_latitude</code> 和 <code>dropoff_latitude</code> 是纬度, 纬度所代表的是纵轴, 也就是 Y 轴</p>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">业务场景</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="paragraph">
<p>在网约车出现之前, 出行很大一部分要靠出租车和公共交通, 所以经常会见到一些情况, 比如说从东直门打车, 告诉师傅要去昌平, 师傅可能拒载. 这种情况所凸显的是一个出租车调度的难题, 所以需要先通过数据来看到问题, 后解决问题.</p>
</div>
<div class="paragraph">
<p>所以要统计出租车利用率, 也就是有乘客乘坐的时间, 和无乘客空跑的时间比例. 这是一个理解出租车的重要指标, 影响利用率的一个因素就是目的地, 比如说, 去昌平, 可能出租车师傅不确定自己是否要空放回来, 而去国贸, 下车几分钟内, 一定能有新的顾客上车.</p>
</div>
<div class="paragraph">
<p>而统计利用率的时候, 需要用到时间数据和空间数据来进行计算, 对于时间计算来说, SparkSQL 提供了很多工具和函数可以使用, 而空间计算仍然是一个比较专业的场景, 需要使用到第三方库.</p>
</div>
<div class="paragraph">
<p>我们的需求是, 在上述的数据集中, 根据时间算出等待时间, 根据地点落地到某个区, 算出某个区的平均等待时间, 也就是这个下车地点对于出租车利用率的影响.</p>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">技术点和其它技术的关系</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>数据清洗</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>数据清洗在几乎所有类型的项目中都会遇到, 处理数据的类型, 处理空值等问题</p>
</div>
</div>
</div>
</li>
<li>
<p>JSON 解析</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p><code>JSON</code> 解析在大部分业务系统的数据分析中都会用到, 如何读取 JSON 数据, 如何把 JSON 数据变为可以使用的对象数据</p>
</div>
</div>
</div>
</li>
<li>
<p>地理位置信息处理</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>地理位置信息的处理是一个比较专业的场景, 在一些租车网站, 或者像滴滴, <code>Uber</code> 之类的出行服务上, 也经常会处理地理位置信息</p>
</div>
</div>
</div>
</li>
<li>
<p>探索性数据分析</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>从拿到一个数据集, 明确需求以后, 如何逐步了解数据集, 如何从数据集中探索对应的内容等, 是一个数据工程师的基本素质</p>
</div>
</div>
</div>
</li>
<li>
<p>会话分析</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>会话分析用于识别同一个用户的多个操作之间的关联, 是分析系统常见的分析模式, 在电商和搜索引擎中非常常见</p>
</div>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">在这个小节中希望大家掌握的知识</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p><code>SparkSQL</code> 中对于类型的处理</p>
</li>
<li>
<p><code>Scala</code> 中常见的 <code>JSON</code> 解析工具</p>
</li>
<li>
<p><code>GeoJson</code> 的使用</p>
</li>
</ol>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_2_流程分析">2. 流程分析</h2>
<div class="sectionbody">
<div class="exampleblock">
<div class="title">导读</div>
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>分析的步骤和角度</p>
</li>
<li>
<p>流程</p>
</li>
</ol>
</div>
</div>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1">分析的视角</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>理解数据集</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>首先要理解数据集, 要回答自己一些问题</p>
</div>
<div class="ulist">
<ul>
<li>
<p>这个数据集是否以行作为单位, 是否是 <code>DataFrame</code> 可以处理的, 大部分情况下都是</p>
</li>
<li>
<p>这个数据集每行记录所代表的实体对象是什么, 例如: 出租车的载客记录</p>
</li>
<li>
<p>表达这个实体对象的最核心字段是什么, 例如: 上下车地点和时间, 唯一标识一辆车的 <code>License</code></p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>理解需求和结果集</p>
<div class="openblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>小学的时候, 有一次考试考的比较差, 老师在帮我分析的时候, 告诉我, 你下次要读懂题意, 再去大题, 这样不会浪费时间, 于是这个信念贯穿了我这些年的工作.</p>
</li>
<li>
<p>按照我对开发工作的理解, 在一开始的阶段进行一个大概的思考和面向对象的设计, 并不会浪费时间, 即使这些设计可能会占用一些时间.</p>
</li>
<li>
<p>对代码的追求也不会浪费时间, 把代码写好, 会减少阅读成本, 沟通成本.</p>
</li>
<li>
<p>对测试的追求也不会浪费时间, 因为在进行回归测试的时候, 可以尽可能的减少修改对已有代码的冲击.</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>所以第一点, 理解需求再动手, 绝对不会浪费时间. 第二点, 在数据分析的任务中, 如何无法理解需求, 可能根本无从动手.</p>
</div>
<div class="ulist">
<ul>
<li>
<p>我们的需求是: <strong>出租车在某个地点的平均等待客人时间</strong></p>
</li>
<li>
<p>简单来说, 结果集中应该有的列: <strong>地点, 平均等待时间</strong></p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>反推每一个步骤</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>结果集中, 应该有的字段有两个, 一个是地点, 一个是等待时间</p>
</div>
<div class="paragraph">
<p>地点如何获知? 其实就是乘客的下车点, 但是是一个坐标, 如何得到其在哪个区?
等待时间如何获知? 其实就是上一个乘客下车, 到下一个乘客上车之间的时间, 通过这两个时间的差值便可获知</p>
</div>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">步骤分析</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>读取数据集</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>数据集很大, 所以我截取了一小部分, 大概百分之一左右, 如果大家感兴趣的话, 可以将完整数据集放在集群中, 使用集群来计算 "大数据"</p>
</div>
</div>
</div>
</li>
<li>
<p>清洗</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>数据集当中的某些列名可能使用起来不方便, 或者数据集当中某些列的值类型可能不对, 或者数据集中有可能存在缺失值, 这些都是要清洗的动机, 和理由</p>
</div>
</div>
</div>
</li>
<li>
<p>增加区域列</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>由于最终要统计的结果是按照区域作为单位, 而不是一个具体的目的地点, 所以要在数据集中增加列中放置区域信息</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>既然是放置行政区名字, 应该现有行政区以及其边界的信息</p>
</li>
<li>
<p>通过上下车的坐标点, 可以判断是否存在于某个行政区中</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>这些判断坐标点是否属于某个区域, 这些信息, 就是专业的领域了</p>
</div>
</div>
</div>
</li>
<li>
<p>按照区域, 统计司机两次营运记录之间的时间差</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>数据集中存在很多出租车师傅的数据, 所以如何将某个师傅的记录发往一个分区, 在这个分区上完成会话分析呢? 这也是一个需要理解的点</p>
</div>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_3_数据读取">3. 数据读取</h2>
<div class="sectionbody">
<div class="exampleblock">
<div class="title">导读</div>
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>工程搭建</p>
</li>
<li>
<p>数据读取</p>
</li>
</ol>
</div>
</div>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1">工程搭建</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>创建 Maven 工程</p>
</li>
<li>
<p>导入 Maven 配置</p>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="xml" class="language-xml hljs">&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;
    &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;

    &lt;groupId&gt;cn.itcast&lt;/groupId&gt;
    &lt;artifactId&gt;taxi&lt;/artifactId&gt;
    &lt;version&gt;0.0.1&lt;/version&gt;

    &lt;properties&gt;
        &lt;scala.version&gt;2.11.8&lt;/scala.version&gt;
        &lt;spark.version&gt;2.2.0&lt;/spark.version&gt;
        &lt;hadoop.version&gt;2.7.5&lt;/hadoop.version&gt;
        &lt;slf4j.version&gt;1.7.16&lt;/slf4j.version&gt;
        &lt;log4j.version&gt;1.2.17&lt;/log4j.version&gt;
        &lt;mysql.version&gt;5.1.35&lt;/mysql.version&gt;
        &lt;esri.version&gt;2.2.2&lt;/esri.version&gt;
        &lt;json4s.version&gt;3.6.6&lt;/json4s.version&gt;
    &lt;/properties&gt;

    &lt;dependencies&gt;
        &lt;!-- Scala 库 --&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.scala-lang&lt;/groupId&gt;
            &lt;artifactId&gt;scala-library&lt;/artifactId&gt;
            &lt;version&gt;${scala.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.scala-lang.modules&lt;/groupId&gt;
            &lt;artifactId&gt;scala-xml_2.11&lt;/artifactId&gt;
            &lt;version&gt;1.0.6&lt;/version&gt;
        &lt;/dependency&gt;

        &lt;!-- Spark 系列包 --&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.apache.spark&lt;/groupId&gt;
            &lt;artifactId&gt;spark-core_2.11&lt;/artifactId&gt;
            &lt;version&gt;${spark.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.apache.spark&lt;/groupId&gt;
            &lt;artifactId&gt;spark-sql_2.11&lt;/artifactId&gt;
            &lt;version&gt;${spark.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.apache.hadoop&lt;/groupId&gt;
            &lt;artifactId&gt;hadoop-client&lt;/artifactId&gt;
            &lt;version&gt;${hadoop.version}&lt;/version&gt;
        &lt;/dependency&gt;

        &lt;!-- 地理位置处理库 --&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;com.esri.geometry&lt;/groupId&gt;
            &lt;artifactId&gt;esri-geometry-api&lt;/artifactId&gt;
            &lt;version&gt;${esri.version}&lt;/version&gt;
        &lt;/dependency&gt;

        &lt;!-- JSON 解析库 --&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.json4s&lt;/groupId&gt;
            &lt;artifactId&gt;json4s-native_2.11&lt;/artifactId&gt;
            &lt;version&gt;${json4s.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.json4s&lt;/groupId&gt;
            &lt;artifactId&gt;json4s-jackson_2.11&lt;/artifactId&gt;
            &lt;version&gt;${json4s.version}&lt;/version&gt;
        &lt;/dependency&gt;

        &lt;!-- 日志相关 --&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.slf4j&lt;/groupId&gt;
            &lt;artifactId&gt;jcl-over-slf4j&lt;/artifactId&gt;
            &lt;version&gt;${slf4j.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.slf4j&lt;/groupId&gt;
            &lt;artifactId&gt;slf4j-api&lt;/artifactId&gt;
            &lt;version&gt;${slf4j.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.slf4j&lt;/groupId&gt;
            &lt;artifactId&gt;slf4j-log4j12&lt;/artifactId&gt;
            &lt;version&gt;${slf4j.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;log4j&lt;/groupId&gt;
            &lt;artifactId&gt;log4j&lt;/artifactId&gt;
            &lt;version&gt;${log4j.version}&lt;/version&gt;
        &lt;/dependency&gt;
    &lt;/dependencies&gt;

    &lt;build&gt;
        &lt;sourceDirectory&gt;src/main/scala&lt;/sourceDirectory&gt;

        &lt;plugins&gt;
            &lt;plugin&gt;
                &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;
                &lt;artifactId&gt;maven-compiler-plugin&lt;/artifactId&gt;
                &lt;version&gt;3.0&lt;/version&gt;
                &lt;configuration&gt;
                    &lt;source&gt;1.8&lt;/source&gt;
                    &lt;target&gt;1.8&lt;/target&gt;
                    &lt;encoding&gt;UTF-8&lt;/encoding&gt;
                &lt;/configuration&gt;
            &lt;/plugin&gt;

            &lt;plugin&gt;
                &lt;groupId&gt;net.alchim31.maven&lt;/groupId&gt;
                &lt;artifactId&gt;scala-maven-plugin&lt;/artifactId&gt;
                &lt;version&gt;3.2.0&lt;/version&gt;
                &lt;executions&gt;
                    &lt;execution&gt;
                        &lt;goals&gt;
                            &lt;goal&gt;compile&lt;/goal&gt;
                            &lt;goal&gt;testCompile&lt;/goal&gt;
                        &lt;/goals&gt;
                        &lt;configuration&gt;
                            &lt;args&gt;
                                &lt;arg&gt;-dependencyfile&lt;/arg&gt;
                                &lt;arg&gt;${project.build.directory}/.scala_dependencies&lt;/arg&gt;
                            &lt;/args&gt;
                        &lt;/configuration&gt;
                    &lt;/execution&gt;
                &lt;/executions&gt;
            &lt;/plugin&gt;
        &lt;/plugins&gt;

    &lt;/build&gt;
&lt;/project&gt;</code></pre>
</div>
</div>
</li>
<li>
<p>创建 Scala 源码目录 <code>src/main/scala</code></p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>并且设置这个目录为 <code>Source Root</code></p>
</div>
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190602150555.png" alt="20190602150555" width="500">
</div>
</div>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">创建文件, 数据读取</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1"><code>Step 1</code>: 创建文件</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>创建 Spark Application 主类 <code>cn.itcast.taxi.TaxiAnalysisRunner</code></p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">package cn.itcast.taxi

object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {

  }
}</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 2</code>: 数据读取</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">数据读取之前要做两件事</dt>
<dd>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>初始化环境, 导入必备的一些包</p>
</li>
<li>
<p>在工程根目录中创建 <code>dataset</code> 文件夹, 并拷贝数据集进去</p>
</li>
</ol>
</div>
</dd>
<dt class="hdlist1">代码如下</dt>
<dd>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    // 1. 创建 SparkSession
    val spark = SparkSession.builder()
      .master("local[6]")
      .appName("taxi")
      .getOrCreate()

    // 2. 导入函数和隐式转换
    import spark.implicits._
    import org.apache.spark.sql.functions._

    // 3. 读取文件
    val taxiRaw = spark.read
      .option("header", value = true)
      .csv("dataset/half_trip.csv")

    taxiRaw.show()
    taxiRaw.printSchema()
  }
}</code></pre>
</div>
</div>
</dd>
<dt class="hdlist1">运行结果如下</dt>
<dd>
<div class="openblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="text" class="language-text hljs">root
 |-- medallion: string (nullable = true)
 |-- hack_license: string (nullable = true)
 |-- vendor_id: string (nullable = true)
 |-- rate_code: string (nullable = true)
 |-- store_and_fwd_flag: string (nullable = true)
 |-- pickup_datetime: string (nullable = true)
 |-- dropoff_datetime: string (nullable = true)
 |-- passenger_count: string (nullable = true)
 |-- trip_time_in_secs: string (nullable = true)
 |-- trip_distance: string (nullable = true)
 |-- pickup_longitude: string (nullable = true)
 |-- pickup_latitude: string (nullable = true)
 |-- dropoff_longitude: string (nullable = true)
 |-- dropoff_latitude: string (nullable = true)</code></pre>
</div>
</div>
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190602153339.png" alt="20190602153339" width="900">
</div>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">下一步</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>剪去多余列</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>现在数据集中包含了一些多余的列, 在后续的计算中并不会使用到, 如果让这些列参与计算的话, 会影响整体性能, 浪费集群资源</p>
</div>
</div>
</div>
</li>
<li>
<p>类型转换</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>可以看到, 现在的数据集中, 所有列类型都是 <code>String</code>, 而在一些统计和运算中, 不能使用 <code>String</code> 来进行, 所以要将这些数据转为对应的类型</p>
</div>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_5_数据清洗">5. 数据清洗</h2>
<div class="sectionbody">
<div class="exampleblock">
<div class="title">导读</div>
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>将 <code>Row</code> 对象转为 <code>Trip</code></p>
</li>
<li>
<p>处理转换过程中的报错</p>
</li>
</ol>
</div>
</div>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1">数据转换</dt>
</dl>
</div>
<div class="sidebarblock">
<div class="content">
<div class="paragraph">
<p>通过 <code>DataFrameReader</code> 读取出来的数据集是 <code>DataFrame</code>, 而 <code>DataFrame</code> 中保存的是 <code>Row</code> 对象, 但是后续我们在进行处理的时候可能要使用到一些有类型的转换, 也需要每一列数据对应自己的数据类型, 所以, 需要将 <code>Row</code> 所代表的弱类型对象转为 <code>Trip</code> 这样的强类型对象, 而 <code>Trip</code> 对象则是一个样例类, 用于代表一个出租车的行程</p>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1"><code>Step 1</code>: 创建 <code>Trip</code> 样例类</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p><code>Trip</code> 是一个强类型的样例类, 一个 <code>Trip</code> 对象代表一个出租车行程, 使用 <code>Trip</code> 可以对应数据集中的一条记录</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    // 此处省略 Main 方法中内容
  }

}

/**
  * 代表一个行程, 是集合中的一条记录
  * @param license 出租车执照号
  * @param pickUpTime 上车时间
  * @param dropOffTime 下车时间
  * @param pickUpX 上车地点的经度
  * @param pickUpY 上车地点的纬度
  * @param dropOffX 下车地点的经度
  * @param dropOffY 下车地点的纬度
  */
case class Trip(
  license: String,
  pickUpTime: Long,
  dropOffTime: Long,
  pickUpX: Double,
  pickUpY: Double,
  dropOffX: Double,
  dropOffY: Double
)</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 2</code>: 将 <code>Row</code> 对象转为 <code>Trip</code> 对象, 从而将 <code>DataFrame</code> 转为 <code>Dataset[Trip]</code></dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>首先应该创建一个新方法来进行这种转换, 毕竟是一个比较复杂的转换操作, 不能怠慢</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    // ... 省略数据读取

    // 4. 数据转换和清洗
    val taxiParsed = taxiRaw.rdd.map(parse)
  }

  /**
    * 将 Row 对象转为 Trip 对象, 从而将 DataFrame 转为 Dataset[Trip] 方便后续操作
    * @param row DataFrame 中的 Row 对象
    * @return 代表数据集中一条记录的 Trip 对象
    */
  def parse(row: Row): Trip = {

  }
}

case class Trip(...)</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 3</code>: 创建 <code>Row</code> 对象的包装类型</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>因为在针对 <code>Row</code> 类型对象进行数据转换时, 需要对一列是否为空进行判断和处理, 在 <code>Scala</code> 中为空的处理进行一些支持和封装, 叫做 <code>Option</code>, 所以在读取 <code>Row</code> 类型对象的时候, 要返回 <code>Option</code> 对象, 通过一个包装类, 可以轻松做到这件事</p>
</div>
<div class="paragraph">
<p>创建一个类 <code>RichRow</code> 用以包装 <code>Row</code> 类型对象, 从而实现 <code>getAs</code> 的时候返回 <code>Option</code> 对象</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    // ...

    // 4. 数据转换和清洗
    val taxiParsed = taxiRaw.rdd.map(parse)
  }

  def parse(row: Row): Trip = {...}

}

case class Trip(...)


class RichRow(row: Row) {

  def getAs[T](field: String): Option[T] = {
    if (row.isNullAt(row.fieldIndex(field)) || StringUtils.isBlank(row.getAs[String](field))) {
      None
    } else {
      Some(row.getAs[T](field))
    }
  }
}</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 4</code>: 转换</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>流程已经存在, 并且也已经为空值处理做了支持, 现在就可以进行转换了</p>
</div>
<div class="paragraph">
<p>首先根据数据集的情况会发现, 有如下几种类型的信息需要处理</p>
</div>
<div class="ulist">
<ul>
<li>
<p>字符串类型</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>执照号就是字符串类型, 对于字符串类型, 只需要判断空, 不需要处理, 如果是空字符串, 加入数据集的应该是一个 <code>null</code></p>
</div>
</div>
</div>
</li>
<li>
<p>时间类型</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>上下车时间就是时间类型, 对于时间类型需要做两个处理</p>
</div>
<div class="ulist">
<ul>
<li>
<p>转为时间戳, 比较容易处理</p>
</li>
<li>
<p>如果时间非法或者为空, 则返回 <code>0L</code></p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p><code>Double</code> 类型</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>上下车的位置信息就是 <code>Double</code> 类型, <code>Double</code> 类型的数据在数据集中以 <code>String</code> 的形式存在, 所以需要将 <code>String</code> 类型转为 <code>Double</code> 类型</p>
</div>
</div>
</div>
</li>
</ul>
</div>
<div class="paragraph">
<p>总结来看, 有两类数据需要特殊处理, 一类是时间类型, 一类是 <code>Double</code> 类型, 所以需要编写两个处理数据的帮助方法, 后在 <code>parse</code> 方法中收集为 <code>Trip</code> 类型对象</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    // ...

    // 4. 数据转换和清洗
    val taxiParsed = taxiRaw.rdd.map(parse)
  }

  def parse(row: Row): Trip = {
    // 通过使用转换方法依次转换各个字段数据
    val row = new RichRow(row)
    val license = row.getAs[String]("hack_license").orNull
    val pickUpTime = parseTime(row, "pickup_datetime")
    val dropOffTime = parseTime(row, "dropoff_datetime")
    val pickUpX = parseLocation(row, "pickup_longitude")
    val pickUpY = parseLocation(row, "pickup_latitude")
    val dropOffX = parseLocation(row, "dropoff_longitude")
    val dropOffY = parseLocation(row, "dropoff_latitude")

    // 创建 Trip 对象返回
    Trip(license, pickUpTime, dropOffTime, pickUpX, pickUpY, dropOffX, dropOffY)
  }

  /**
    * 将时间类型数据转为时间戳, 方便后续的处理
    * @param row 行数据, 类型为 RichRow, 以便于处理空值
    * @param field 要处理的时间字段所在的位置
    * @return 返回 Long 型的时间戳
    */
  def parseTime(row: RichRow, field: String): Long = {
    val pattern = "yyyy-MM-dd HH:mm:ss"
    val formatter = new SimpleDateFormat(pattern, Locale.ENGLISH)

    val timeOption = row.getAs[String](field)
    timeOption.map( time =&gt; formatter.parse(time).getTime )
      .getOrElse(0L)
  }

  /**
    * 将字符串标识的 Double 数据转为 Double 类型对象
    * @param row 行数据, 类型为 RichRow, 以便于处理空值
    * @param field 要处理的 Double 字段所在的位置
    * @return 返回 Double 型的时间戳
    */
  def parseLocation(row: RichRow, field: String): Double = {
    row.getAs[String](field).map( loc =&gt; loc.toDouble ).getOrElse(0.0D)
  }
}

case class Trip(..)

class RichRow(row: Row) {...}</code></pre>
</div>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1">异常处理</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="paragraph">
<p>在进行类型转换的时候, 是一个非常容易错误的点, 需要进行单独的处理</p>
</div>
<div class="dlist">
<dl>
<dt class="hdlist1"><code>Step 1</code>: 思路</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190603015655.png" alt="20190603015655">
</div>
</div>
<div class="paragraph">
<p><code>parse</code> 方法应该做的事情应该有两件</p>
</div>
<div class="ulist">
<ul>
<li>
<p>捕获异常</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>异常一定是要捕获的, 无论是否要抛给 <code>DataFrame</code>, 都要先捕获一下, 获知异常信息</p>
</div>
<div class="paragraph">
<p>捕获要使用 <code>try &#8230;&#8203; catch &#8230;&#8203;</code> 代码块</p>
</div>
</div>
</div>
</li>
<li>
<p>返回结果</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>返回结果应该分为两部分来进行说明</p>
</div>
<div class="ulist">
<ul>
<li>
<p>正确, 正确则返回数据</p>
</li>
<li>
<p>错误, 则应该返回两类信息, 一 告知外面哪个数据出了错, 二 告知错误是什么</p>
</li>
</ul>
</div>
</div>
</div>
</li>
</ul>
</div>
<div class="paragraph">
<p>对于这种情况, 可以使用 <code>Scala</code> 中提供的一个类似于其它语言中多返回值的 <code>Either</code>. <code>Either</code> 分为两个情况, 一个是 <code>Left</code>, 一个是 <code>Right</code>, 左右两个结果所代表的意思可有由用户来指定</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val process = (b: Double) =&gt; {       <i class="conum" data-value="1"></i><b>(1)</b>
  val a = 10.0
  a / b
}

def safe(function: Double =&gt; Double, b: Double): Either[Double, (Double, Exception)] = {  <i class="conum" data-value="2"></i><b>(2)</b>
  try {
    val result = function(b)         <i class="conum" data-value="3"></i><b>(3)</b>
    Left(result)
  } catch {
    case e: Exception =&gt; Right(b, e) <i class="conum" data-value="4"></i><b>(4)</b>
  }
}

val result = safe(process, 0)        <i class="conum" data-value="5"></i><b>(5)</b>

result match {                       <i class="conum" data-value="6"></i><b>(6)</b>
  case Left(r) =&gt; println(r)
  case Right((b, e)) =&gt; println(b, e)
}</code></pre>
</div>
</div>
<div class="colist arabic">
<table>
<tr>
<td><i class="conum" data-value="1"></i><b>1</b></td>
<td>一个函数, 接收一个参数, 根据参数进行除法运算</td>
</tr>
<tr>
<td><i class="conum" data-value="2"></i><b>2</b></td>
<td>一个方法, 作用是让 <code>process</code> 函数调用起来更安全, 在其中 <code>catch</code> 错误, 报错后返回足够的信息 (报错时的参数和报错信息)</td>
</tr>
<tr>
<td><i class="conum" data-value="3"></i><b>3</b></td>
<td>正常时返回 <code>Left</code>, 放入正确结果</td>
</tr>
<tr>
<td><i class="conum" data-value="4"></i><b>4</b></td>
<td>异常时返回 <code>Right</code>, 放入报错时的参数, 和报错信息</td>
</tr>
<tr>
<td><i class="conum" data-value="5"></i><b>5</b></td>
<td>外部调用</td>
</tr>
<tr>
<td><i class="conum" data-value="6"></i><b>6</b></td>
<td>处理调用结果, 如果是 Right 的话, 则可以进行响应的异常处理和弥补</td>
</tr>
</table>
</div>
<div class="paragraph">
<p><code>Either</code> 和 <code>Option</code> 比较像, 都是返回不同的情况, 但是 <code>Either</code> 的 <code>Right</code> 可以返回多个值, 而 <code>None</code> 不行</p>
</div>
<div class="paragraph">
<p>如果一个 <code>Either</code> 有两个结果的可能性, 一个是 <code>Left[L]</code>, 一个是 <code>Right[R]</code>, 则 <code>Either</code> 的范型是 <code>Either[L, R]</code></p>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 2</code>: 完成代码逻辑</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>加入一个 Safe 方法, 更安全</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    // ...

    // 4. 数据转换和清洗
    val taxiParsed = taxiRaw.rdd.map(safe(parse))
  }

  /**
    * 包裹转换逻辑, 并返回 Either
    */
  def safe[P, R](f: P =&gt; R): P =&gt; Either[R, (P, Exception)] = {
    new Function[P, Either[R, (P, Exception)]] with Serializable {
      override def apply(param: P): Either[R, (P, Exception)] = {
        try {
          Left(f(param))
        } catch {
          case e: Exception =&gt; Right((param, e))
        }
      }
    }
  }

  def parse(row: Row): Trip = {...}

  def parseTime(row: RichRow, field: String): Long = {...}

  def parseLocation(row: RichRow, field: String): Double = {...}
}

case class Trip(..)

class RichRow(row: Row) {...}</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 3</code>: 针对转换异常进行处理</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>对于 <code>Either</code> 来说, 可以获取 <code>Left</code> 中的数据, 也可以获取 <code>Right</code> 中的数据, 只不过如果当 <code>Either</code> 是一个 Right 实例时候, 获取 <code>Left</code> 的值会报错</p>
</div>
<div class="paragraph">
<p>所以, 针对于 <code>Dataset[Either]</code> 可以有如下步骤</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>试运行, 观察是否报错</p>
</li>
<li>
<p>如果报错, 则打印信息解决报错</p>
</li>
<li>
<p>如果解决不了, 则通过 <code>filter</code> 过滤掉 <code>Right</code></p>
</li>
<li>
<p>如果没有报错, 则继续向下运行</p>
</li>
</ol>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    ...

    // 4. 数据转换和清洗
    val taxiParsed = taxiRaw.rdd.map(safe(parse))
    val taxiGood = taxiParsed.map( either =&gt; either.left.get ).toDS()
  }

  ...
}

...</code></pre>
</div>
</div>
<div class="paragraph">
<p>很幸运, 在运行上面的代码时, 没有报错, 如果报错的话, 可以使用如下代码进行过滤</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    ...

    // 4. 数据转换和清洗
    val taxiParsed = taxiRaw.rdd.map(safe(parse))
    val taxiGood = taxiParsed.filter( either =&gt; either.isLeft )
      .map( either =&gt; either.left.get )
      .toDS()
  }

  ...
}

...</code></pre>
</div>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">观察数据集的时间分布</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="paragraph">
<p>观察数据分布常用手段是直方图, 直方图反应的是数据的 <code>"数量"</code> 分布</p>
</div>
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190603113500.png" alt="20190603113500" width="500">
</div>
</div>
<div class="paragraph">
<p>通过这个图可以看到其实就是乘客年龄的分布, 横轴是乘客的年龄, 纵轴是乘客年龄的频数分布</p>
</div>
<div class="paragraph">
<p>因为我们这个项目中要对出租车利用率进行统计, 所以需要先看一看单次行程的时间分布情况, 从而去掉一些异常数据, 保证数据是准确的</p>
</div>
<div class="paragraph">
<p>绘制直方图的 "图" 留在后续的 <code>DMP</code> 项目中再次介绍, 现在先准备好直方图所需要的数据集, 通过数据集来观察即可, 直方图需要的是两个部分的内容, 一个是数据本身, 另外一个是数据的分布, 也就是频数的分布, 步骤如下</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>计算每条数据的时长, 但是单位要有变化, 按照分钟, 或者小时来作为时长单位</p>
</li>
<li>
<p>统计每个时长的数据量, 例如有 <code>500</code> 个行程是一小时内完成的, 有 <code>300</code> 个行程是 <code>1 - 2</code> 小时内完成</p>
</li>
</ol>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">统计时间分布直方图</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">使用 <code>UDF</code> 的优点和代价</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p><code>UDF</code> 是一个很好用的东西, 特别好用, 对整体的逻辑实现会变得更加简单可控, 但是有两个非常明显的缺点, 所以在使用的时候要注意, 虽然有这两个缺点, 但是只在必要的地方使用就没什么问题, 对于逻辑的实现依然是有很大帮助的</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p><code>UDF</code> 中, 对于空值的处理比较麻烦</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>例如一个 <code>UDF</code> 接收两个参数, 是 <code>Scala</code> 中的 <code>Int</code> 类型和 <code>Double</code> 类型, 那么, 在传入 <code>UDF</code> 参数的时候, 如果有数据为 <code>null</code>, 就会出现转换异常</p>
</div>
</div>
</div>
</li>
<li>
<p>使用 <code>UDF</code> 的时候, 优化器可能无法对其进行优化</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p><code>UDF</code> 对于 <code>Catalyst</code> 是不透明的, <code>Catalyst</code> 不可获知 <code>UDF</code> 中的逻辑, 但是普通的 <code>Function</code> 对于 <code>Catalyst</code> 是透明的, <code>Catalyst</code> 可以对其进行优化</p>
</div>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 1</code>: 编写 <code>UDF</code>, 将行程时长由毫秒单位改为小时单位</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>定义 <code>UDF</code>, 在 <code>UDF</code> 中做两件事</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>计算行程时长</p>
</li>
<li>
<p>将时长由毫秒转为分钟</p>
</li>
</ol>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    ...

    // 5. 过滤行程无效的数据
    val hours = (pickUp: Long, dropOff: Long) =&gt; {
      val duration = dropOff - pickUp
      TimeUnit.HOURS.convert(, TimeUnit.MILLISECONDS)
    }
    val hoursUDF = udf(hours)
  }

  ...
}</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 2:</code> 统计时长分布</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>第一步应该按照行程时长进行分组</p>
</li>
<li>
<p>求得每个分组的个数</p>
</li>
<li>
<p>最后按照时长排序并输出结果</p>
</li>
</ol>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    ...

    // 5. 过滤行程无效的数据
    val hours = (pickUp: Long, dropOff: Long) =&gt; {
      val duration = dropOff - pickUp
      TimeUnit.MINUTES.convert(, TimeUnit.MILLISECONDS)
    }
    val hoursUDF = udf(hours)

    taxiGood.groupBy(hoursUDF($"pickUpTime", $"dropOffTime").as("duration"))
      .count()
      .sort("duration")
      .show()
  }

  ...
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>会发现, 大部分时长都集中在 <code>1 - 19</code> 分钟内</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="text" class="language-text hljs">+--------+-----+
|duration|count|
+--------+-----+
|       0|   86|
|       1|  140|
|       2|  383|
|       3|  636|
|       4|  759|
|       5|  838|
|       6|  791|
|       7|  761|
|       8|  688|
|       9|  625|
|      10|  537|
|      11|  499|
|      12|  395|
|      13|  357|
|      14|  353|
|      15|  264|
|      16|  252|
|      17|  197|
|      18|  181|
|      19|  136|
+--------+-----+</code></pre>
</div>
</div>
</div>
</div>
</dd>
<dt class="hdlist1"><code>Step 3:</code> 注册函数, 在 SQL 表达式中过滤数据</dt>
<dd>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>大部分时长都集中在 <code>1 - 19</code> 分钟内, 所以这个范围外的数据就可以去掉了, 如果同学使用完整的数据集, 会发现还有一些负的时长, 好像是回到未来的场景一样, 对于这种非法的数据, 也要过滤掉, 并且还要分析原因</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object TaxiAnalysisRunner {

  def main(args: Array[String]): Unit = {
    ...

    // 5. 过滤行程无效的数据
    val hours = (pickUp: Long, dropOff: Long) =&gt; {
      val duration = dropOff - pickUp
      TimeUnit.MINUTES.convert(, TimeUnit.MILLISECONDS)
    }
    val hoursUDF = udf(hours)

    taxiGood.groupBy(hoursUDF($"pickUpTime", $"dropOffTime").as("duration"))
      .count()
      .sort("duration")
      .show()

    spark.udf.register("hours", hours)
    val taxiClean = taxiGood.where("hours(pickUpTime, dropOffTime) BETWEEN 0 AND 3")
    taxiClean.show()
  }

  ...
}</code></pre>
</div>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_6_行政区信息">6. 行政区信息</h2>
<div class="sectionbody">
<div class="dlist">
<dl>
<dt class="hdlist1">目标和步骤</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">目标</dt>
<dd>
<div class="paragraph">
<p>能够通过 <code>GeoJSON</code> 判断一个点是否在一个区域内, 能够使用 <code>JSON4S</code> 解析 <code>JSON</code> 数据</p>
</div>
</dd>
<dt class="hdlist1">步骤</dt>
<dd>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>需求介绍</p>
</li>
<li>
<p>工具介绍</p>
</li>
<li>
<p>解析 <code>JSON</code></p>
</li>
<li>
<p>读取 <code>Geometry</code></p>
</li>
</ol>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">总结</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>整体流程</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>JSON4S 介绍</p>
</li>
<li>
<p>ESRI 介绍</p>
</li>
<li>
<p>编写函数实现 <code>经纬度 &#8594; Geometry</code> 转换</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>后续可以使用函数来进行转换, 并且求得时间差</p>
</li>
</ul>
</div>
</div>
</div>
</dd>
</dl>
</div>
<div class="sect2">
<h3 id="_6_1_需求介绍">6.1. 需求介绍</h3>
<div class="dlist">
<dl>
<dt class="hdlist1">目标和步骤</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">目标</dt>
<dd>
<div class="paragraph">
<p>理解表示地理位置常用的 GeoJSON</p>
</div>
</dd>
<dt class="hdlist1">步骤</dt>
<dd>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>思路整理</p>
</li>
<li>
<p><code>GeoJSON</code> 是什么</p>
</li>
<li>
<p><code>GeoJSON</code> 的使用</p>
</li>
</ol>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">思路整理</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>需求</p>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>项目的任务是统计出租车在不同行政区的平均等待时间, 所以源数据集和经过计算希望得到的新数据集大致如下</p>
</div>
<div class="ulist">
<ul>
<li>
<p>源数据集</p>
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190812104021.png" alt="20190812104021" width="600">
</div>
</div>
</li>
<li>
<p>目标数据集</p>
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190812104113.png" alt="20190812104113" width="600">
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>目标数据集分析</p>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>目标数据集中有三列, <code>borough</code>, <code>avg(seconds)</code>, <code>stddev_samp(seconds)</code></p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>borough</code> 表示目的地行政区的名称</p>
</li>
<li>
<p><code>avg(seconds)</code> 和 <code>stddev_samp(seconds)</code> 是 <code>seconds</code> 的聚合, <code>seconds</code> 是下车时间和下一次上车时间之间的差值, 代表等待时间</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>所以有两列数据是现在数据集中没有</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>borough</code> 要根据数据集中的经纬度, 求出其行政区的名字</p>
</li>
<li>
<p><code>seconds</code> 要根据数据集中上下车时间, 求出差值</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>求出 <code>borough</code></p>
<div class="openblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>读取行政区位置信息</p>
</li>
<li>
<p>搜索每一条数据的下车经纬度所在的行政区</p>
</li>
<li>
<p>在数据集中添加行政区列</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>求出 <code>seconds</code></p>
</li>
<li>
<p>根据 <code>borough</code> 计算平均等待时间, 是一个聚合操作</p>
</li>
</ol>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">GeoJSON 是什么</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>定义</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p><code>GeoJSON</code> 是一种基于 <code>JSON</code> 的开源标准格式, 用来表示地理位置信息</p>
</li>
<li>
<p>其中定了很多对象, 表示不同的地址位置单位</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>如何表示地理位置</p>
<div class="exampleblock">
<div class="content">
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 10%;">
<col style="width: 15%;">
<col>
</colgroup>
<thead>
<tr>
<th class="tableblock halign-center valign-middle">类型</th>
<th class="tableblock halign-center valign-middle" colspan="2">例子</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-center valign-middle"><p class="tableblock">点</p></td>
<td class="tableblock halign-center valign-middle"><div class="content"><div class="imageblock">
<div class="content">
<img src="https://upload.wikimedia.org/wikipedia/commons/thumb/c/c2/SFA_Point.svg/51px-SFA_Point.svg.png" alt="51px SFA Point.svg">
</div>
</div></div></td>
<td class="tableblock halign-left valign-top"><div class="content"><div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="json" class="language-json hljs">{
    "type": "Point",
    "coordinates": [30, 10]
}</code></pre>
</div>
</div></div></td>
</tr>
<tr>
<td class="tableblock halign-center valign-middle"><p class="tableblock">线段</p></td>
<td class="tableblock halign-center valign-middle"><div class="content"><div class="imageblock">
<div class="content">
<img src="https://upload.wikimedia.org/wikipedia/commons/thumb/b/b9/SFA_LineString.svg/51px-SFA_LineString.svg.png" alt="51px SFA LineString.svg">
</div>
</div></div></td>
<td class="tableblock halign-left valign-top"><div class="content"><div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="json" class="language-json hljs">{
    "type": "Point",
    "coordinates": [30, 10]
}</code></pre>
</div>
</div></div></td>
</tr>
<tr>
<td class="tableblock halign-center valign-middle" rowspan="2"><p class="tableblock">多边形</p></td>
<td class="tableblock halign-center valign-middle"><div class="content"><div class="imageblock">
<div class="content">
<img src="https://upload.wikimedia.org/wikipedia/commons/thumb/b/b9/SFA_LineString.svg/51px-SFA_LineString.svg.png" alt="51px SFA LineString.svg">
</div>
</div></div></td>
<td class="tableblock halign-left valign-top"><div class="content"><div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="json" class="language-json hljs">{
    "type": "Point",
    "coordinates": [30, 10]
}</code></pre>
</div>
</div></div></td>
</tr>
<tr>
<td class="tableblock halign-center valign-middle"><div class="content"><div class="imageblock">
<div class="content">
<img src="https://upload.wikimedia.org/wikipedia/commons/thumb/5/55/SFA_Polygon_with_hole.svg/51px-SFA_Polygon_with_hole.svg.png" alt="51px SFA Polygon with hole.svg">
</div>
</div></div></td>
<td class="tableblock halign-left valign-top"><div class="content"><div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="json" class="language-json hljs">{
    "type": "Polygon",
    "coordinates": [
        [[35, 10], [45, 45], [15, 40], [10, 20], [35, 10]],
        [[20, 30], [35, 35], [30, 20], [20, 30]]
    ]
}</code></pre>
</div>
</div></div></td>
</tr>
</tbody>
</table>
</div>
</div>
</li>
<li>
<p>数据集</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>行政区范围可以使用 <code>GeoJSON</code> 中的多边形来表示</p>
</li>
<li>
<p>课程中为大家提供了一份表示了纽约的各个行政区范围的数据集, 叫做 <code>nyc-borough-boundaries-polygon.geojson</code></p>
<div class="paragraph">
<p><span class="image"><img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190603155616.png" alt="20190603155616" width="500"></span></p>
</div>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>使用步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>创建一个类型 <code>Feature</code>, 对应 <code>JSON</code> 文件中的格式</p>
</li>
<li>
<p>通过解析 <code>JSON</code>, 创建 <code>Feature</code> 对象</p>
</li>
<li>
<p>通过 <code>Feature</code> 对象创建 <code>GeoJSON</code> 表示一个地理位置的 <code>Geometry</code> 对象</p>
</li>
<li>
<p>通过 <code>Geometry</code> 对象判断一个经纬度是否在其范围内</p>
</li>
</ol>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">总结</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>思路</p>
<div class="openblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>从需求出发, 设计结果集</p>
</li>
<li>
<p>推导结果集所欠缺的字段</p>
</li>
<li>
<p>补齐欠缺的字段, 生成结果集, 需求完成</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>后续整体上要做的事情</p>
<div class="openblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>需求是查看出租车在不同行政区的等待客人的时间</p>
</li>
<li>
<p>需要补充两个点, 一是出租车下客点的行政区名称, 二是等待时间</p>
</li>
<li>
<p>本章节聚焦于行政区的信息补充</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>学习步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>介绍 <code>JSON</code> 解析的工具</p>
</li>
<li>
<p>介绍读取 <code>GeoJSON</code> 的工具</p>
</li>
<li>
<p><code>JSON</code> 解析</p>
</li>
<li>
<p>读取 <code>GeoJSON</code></p>
</li>
</ol>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
<div class="sect2">
<h3 id="_6_2_工具介绍">6.2. 工具介绍</h3>
<div class="dlist">
<dl>
<dt class="hdlist1">目标和步骤</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">目标</dt>
<dd>
<div class="paragraph">
<p>理解 <code>JSON</code> 解析和 <code>Geometry</code> 解析所需要的工具, 后续使用这些工具补充行政区信息</p>
</div>
</dd>
<dt class="hdlist1">步骤</dt>
<dd>
<div class="olist arabic">
<ol class="arabic">
<li>
<p><code>JSON4S</code></p>
</li>
<li>
<p><code>ESRI Geometry</code></p>
</li>
</ol>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">JSON4S 介绍</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>介绍</p>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>一般在 <code>Java</code> 中, 常使用如下三个工具解析 <code>JSON</code></p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>Gson</code></p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p><code>Google</code> 开源的 <code>JSON</code> 解析工具, 比较人性化, 易于使用, 但是性能不如 <code>Jackson</code>, 也不如 <code>Jackson</code> 有积淀</p>
</div>
</div>
</div>
</li>
<li>
<p><code>Jackson</code></p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p><code>Jackson</code> 是功能最完整的 <code>JSON</code> 解析工具, 也是最老牌的 <code>JSON</code> 解析工具, 性能也足够好, 但是 <code>API</code> 在一开始支持的比较少, 用起来稍微有点繁琐</p>
</div>
</div>
</div>
</li>
<li>
<p><code>FastJson</code></p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>阿里巴巴的 <code>JSON</code> 开源解析工具, 以快著称, 但是某些方面用起来稍微有点反直觉</p>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>什么是 <code>JSON</code> 解析</p>
<div class="exampleblock">
<div class="content">
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190603161629.png" alt="20190603161629">
</div>
</div>
<div class="ulist">
<ul>
<li>
<p>读取 <code>JSON</code> 数据的时候, 读出来的是一个有格式的字符串, 将这个字符串转换为对象的过程就叫做解析</p>
</li>
<li>
<p>可以使用 <code>JSON4S</code> 来解析 <code>JSON</code>, <code>JSON4S</code> 是一个其它解析工具的 <code>Scala</code> 封装以适应 <code>Scala</code> 的对象转换</p>
</li>
<li>
<p><code>JSON4S</code> 支持 <code>Jackson</code> 作为底层的解析工具</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>Step 1: 导入 <code>Maven</code> 依赖</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="xml" class="language-xml hljs">&lt;!-- JSON4S --&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.json4s&lt;/groupId&gt;
    &lt;artifactId&gt;json4s-native_2.11&lt;/artifactId&gt;
    &lt;version&gt;${json4s.version}&lt;/version&gt;
&lt;/dependency&gt;
&lt;!-- JSON4S 的 Jackson 集成库 --&gt;
&lt;dependency&gt;
    &lt;groupId&gt;org.json4s&lt;/groupId&gt;
    &lt;artifactId&gt;json4s-jackson_2.11&lt;/artifactId&gt;
    &lt;version&gt;${json4s.version}&lt;/version&gt;
&lt;/dependency&gt;</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 2: 解析 <code>JSON</code></p>
<div class="exampleblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">步骤</dt>
<dd>
<div class="openblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>解析 <code>JSON</code> 对象</p>
</li>
<li>
<p>序列化 <code>JSON</code> 对象</p>
</li>
<li>
<p>使用 <code>Jackson</code> 反序列化 <code>Scala</code> 对象</p>
</li>
<li>
<p>使用 <code>Jackson</code> 序列化 <code>Scala</code> 对象</p>
</li>
</ol>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">代码</dt>
<dd>
<div class="openblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">import org.json4s._
import org.json4s.jackson.JsonMethods._
import org.json4s.jackson.Serialization.{read, write}

case class Product(name: String, price: Double)

val product =
  """
    |{"name":"Toy","price":35.35}
  """.stripMargin

// 可以解析 JSON 为对象
val obj: Product = parse(product).extra[Product]

// 可以将对象序列化为 JSON
val str: String = compact(render(Product("电视", 10.5)))

// 使用序列化 API 之前, 要先导入代表转换规则的 formats 对象隐式转换
implicit val formats = Serialization.formats(NoTypeHints)

// 可以使用序列化的方式来将 JSON 字符串反序列化为对象
val obj1 = read[Person](product)

// 可以使用序列化的方式将对象序列化为 JSON 字符串
val str1 = write(Product("电视", 10.5))</code></pre>
</div>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">GeoJSON 读取工具的介绍</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>介绍</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>读取 <code>GeoJSON</code> 的工具有很多, 但是大部分都过于复杂, 有一些只能 <code>Java</code> 中用</p>
</li>
<li>
<p>有一个较为简单, 也没有使用底层 <code>C</code> 语言开发的解析 <code>GeoJSON</code> 的类库叫做 <code>ESRI Geometry</code>, <code>Scala</code> 中也可以支持</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>使用</p>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p><code>ESRI Geometry</code> 的使用比较的简单, 大致就如下这样调用即可</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val mg = GeometryEngine.geometryFromGeoJson(jsonStr, 0, Geometry.Type.Unknown) <i class="conum" data-value="1"></i><b>(1)</b>
val geometry = mg.getGeometry <i class="conum" data-value="2"></i><b>(2)</b>

GeometryEngine.contains(geometry, other, csr) <i class="conum" data-value="3"></i><b>(3)</b></code></pre>
</div>
</div>
<div class="colist arabic">
<table>
<tr>
<td><i class="conum" data-value="1"></i><b>1</b></td>
<td>读取 <code>JSON</code> 生成 <code>Geometry</code> 对象</td>
</tr>
<tr>
<td><i class="conum" data-value="2"></i><b>2</b></td>
<td>重点: 一个 <code>Geometry</code> 对象就表示一个 <code>GeoJSON</code> 支持的对象, 可能是一个点, 也可能是一个多边形</td>
</tr>
<tr>
<td><i class="conum" data-value="3"></i><b>3</b></td>
<td>判断一个 <code>Geometry</code> 中是否包含另外一个 <code>Geometry</code></td>
</tr>
</table>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">总结</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p><code>JSON</code> 解析</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p><code>FastJSON</code> 和 <code>Gson</code> 直接在 <code>Scala</code> 中使用会出现问题, 因为 <code>Scala</code> 的对象体系和 <code>Java</code> 略有不同</p>
</li>
<li>
<p>最为适合 <code>Scala</code> 的方式是使用 <code>JSON4S</code> 作为上层 <code>API</code>, <code>Jackson</code> 作为底层提供 <code>JSON</code> 解析能力, 共同实现 <code>JSON</code> 解析</p>
</li>
<li>
<p>其使用方式非常简单, 两行即可解析</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">implicit val formats = Serialization.formats(NoTypeHints)
val obj = read[Person](product)</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p><code>GeoJSON</code> 的解析</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>有一个很适合 Scala 的 GeoJSON 解析工具, 叫做 <code>ESRI Geometry</code>, 其可以将 GeoJSON 字符串转为 Geometry 对象, 易于使用</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">GeometryEngine.geometryFromGeoJson(jsonStr, 0, Geometry.Type.Unknown)</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>后续工作</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>读取行政区的数据集, 解析 <code>JSON</code> 格式, 将 <code>JSON</code> 格式的字符串转为对象</p>
</li>
<li>
<p>使用 <code>ESRI</code> 的 <code>GeometryEngine</code> 读取行政区的 <code>Geometry</code> 对象的 <code>JSON</code> 字符串, 生成 <code>Geometry</code> 对象</p>
</li>
<li>
<p>使用上车点和下车点的坐标创建 <code>Point</code> 对象 ( <code>Geometry</code> 的子类)</p>
</li>
<li>
<p>判断 <code>Point</code> 是否在行政区的 <code>Geometry</code> 的范围内 (行政区的 <code>Geometry</code> 其实本质上是子类 <code>Polygon</code> 的对象)</p>
</li>
</ol>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
<div class="sect2">
<h3 id="_6_3_具体实现">6.3. 具体实现</h3>
<div class="dlist">
<dl>
<dt class="hdlist1">目标和步骤</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">目标</dt>
<dd>
<div class="paragraph">
<p>通过 <code>JSON4S</code> 和 <code>ESRI</code> 配合解析提供的 <code>GeoJSON</code> 数据集, 获取纽约的每个行政区的范围</p>
</div>
</dd>
<dt class="hdlist1">步骤</dt>
<dd>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>解析 <code>JSON</code></p>
</li>
<li>
<p>使用 <code>ESRI</code> 生成表示行政区的一组 <code>Geometry</code> 对象</p>
</li>
</ol>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">解析 JSON</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>对照 <code>JSON</code> 中的格式, 创建解析的目标类</p>
</li>
<li>
<p>解析 <code>JSON</code> 数据转为目标类的对象</p>
</li>
<li>
<p>读取数据集, 执行解析</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>Step 1: 创建目标类</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p><code>GeoJSON</code></p>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="json" class="language-json hljs">{
	"type": "FeatureCollection",
	"features": [ <i class="conum" data-value="1"></i><b>(1)</b>
    {
      "type": "Feature",
      "id": 0,
      "properties": {
        "boroughCode": 5,
        "borough": "Staten Island",
        "@id": "http:\/\/nyc.pediacities.com\/Resource\/Borough\/Staten_Island"
      },
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [-74.050508064032471, 40.566422034160816],
            [-74.049983525625748, 40.566395924928273]
          ]
        ]
      }
    }
  ]
}</code></pre>
</div>
</div>
<div class="colist arabic">
<table>
<tr>
<td><i class="conum" data-value="1"></i><b>1</b></td>
<td><code>features</code> 是一个数组, 其中每一个 <code>Feature</code> 代表一个行政区</td>
</tr>
</table>
</div>
</li>
<li>
<p>目标类</p>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">case class FeatureCollection(
  features: List[Feature]
)

case class Feature(
  id: Int,
  properties: Map[String, String],
  geometry: JObject
)

case class FeatureProperties(boroughCode: Int, borough: String)</code></pre>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>Step 2: 将 <code>JSON</code> 字符串解析为目标类对象</p>
<div class="exampleblock">
<div class="content">
<div class="paragraph">
<p>创建工具类实现功能</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">object FeatureExtraction {

  def parseJson(json: String): FeatureCollection = {
    implicit val format: AnyRef with Formats = Serialization.formats(NoTypeHints)
    val featureCollection = read[FeatureCollection](json)
    featureCollection
  }
}</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 3: 读取数据集, 转换数据</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val geoJson = Source.fromFile("dataset/nyc-borough-boundaries-polygon.geojson").mkString
val features = FeatureExtraction.parseJson(geoJson)</code></pre>
</div>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">解析 GeoJSON</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>转换 <code>JSON</code> 为 <code>Geometry</code> 对象</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>表示行政区的 JSON 段在哪</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="json" class="language-json hljs">{
	"type": "FeatureCollection",
	"features": [
    {
      "type": "Feature",
      "id": 0,
      "properties": {
        "boroughCode": 5,
        "borough": "Staten Island",
        "@id": "http:\/\/nyc.pediacities.com\/Resource\/Borough\/Staten_Island"
      },
      "geometry": { <i class="conum" data-value="1"></i><b>(1)</b>
        "type": "Polygon",
        "coordinates": [
          [
            [-74.050508064032471, 40.566422034160816],
            [-74.049983525625748, 40.566395924928273]
          ]
        ]
      }
    }
  ]
}</code></pre>
</div>
</div>
<div class="colist arabic">
<table>
<tr>
<td><i class="conum" data-value="1"></i><b>1</b></td>
<td><code>geometry</code> 段即是 <code>Geometry</code> 对象的 <code>JSON</code> 表示</td>
</tr>
</table>
</div>
</div>
</div>
</li>
<li>
<p>通过 <code>ESRI</code> 解析此段</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">case class Feature(
  id: Int,
  properties: Map[String, String],
  geometry: JObject             <i class="conum" data-value="1"></i><b>(1)</b>
) {

  def getGeometry: Geometry = { <i class="conum" data-value="2"></i><b>(2)</b>
    GeometryEngine.geoJsonToGeometry(compact(render(geometry)), 0, Geometry.Type.Unknown).getGeometry
  }
}</code></pre>
</div>
</div>
<div class="colist arabic">
<table>
<tr>
<td><i class="conum" data-value="1"></i><b>1</b></td>
<td><code>geometry</code> 对象需要使用 <code>ESRI</code> 解析并生成, 所以此处并没有使用具体的对象类型, 而是使用 <code>JObject</code> 表示一个 <code>JsonObject</code>, 并没有具体的解析为某个对象, 节省资源</td>
</tr>
<tr>
<td><i class="conum" data-value="2"></i><b>2</b></td>
<td>将 <code>JSON</code> 转为 <code>Geometry</code> 对象</td>
</tr>
</table>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">在出租车 DataFrame 中增加行政区信息</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>将 <code>Geometry</code> 数据集按照区域大小排序</p>
</li>
<li>
<p>广播 <code>Geometry</code> 信息, 发给每一个 <code>Executor</code></p>
</li>
<li>
<p>创建 <code>UDF</code>, 通过经纬度获取行政区信息</p>
</li>
<li>
<p>统计行政区信息</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>Step 1: 排序 <code>Geometry</code></p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>动机: 后续需要逐个遍历 <code>Geometry</code> 对象, 取得每条出租车数据所在的行政区, 大的行政区排在前面效率更好一些</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val areaSortedFeatures = features.features.sortBy(feature =&gt; {
    (feature.properties("boroughCode"), - feature.getGeometry.calculateArea2D())
  })</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 2: 发送广播</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>动机: <code>Geometry</code> 对象数组相对来说是一个小数据集, 后续需要使用 <code>Spark</code> 来进行计算, 将 <code>Geometry</code> 分发给每一个 <code>Executor</code> 会显著减少 <code>IO</code> 通量</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val featuresBc = spark.sparkContext.broadcast(areaSortedFeatures)</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 3: 创建 <code>UDF</code></p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>动机: 创建 UDF, 接收每个出租车数据的下车经纬度, 转为行政区信息, 以便后续实现功能</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val boroughLookUp = (x: Double, y: Double) =&gt; {
  val features: Option[Feature] = featuresBc.value.find(feature =&gt; {
    GeometryEngine.contains(feature.getGeometry, new Point(x, y), SpatialReference.create(4326))
  })
  features.map(feature =&gt; {
    feature.properties("borough")
  }).getOrElse("NA")
}

val boroughUDF = udf(boroughLookUp)</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 4: 测试转换结果, 统计每个行政区的出租车数据数量</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>动机: 写完功能最好先看看, 运行一下</p>
</li>
</ul>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">taxiClean.groupBy(boroughUDF('dropOffX, 'dropOffY))
  .count()
  .show()</code></pre>
</div>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">总结</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>具体的实现分为两个大步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>解析 <code>JSON</code> 生成 <code>Geometry</code> 数据</p>
</li>
<li>
<p>通过 <code>Geometry</code> 数据, 取得每一条出租车数据的行政区信息</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p><code>Geometry</code> 数据的生成又有如下步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>使用 <code>JSON4S</code> 解析行政区区域信息的数据集</p>
</li>
<li>
<p>取得其中每一个行政区信息的 <code>Geometry</code> 区域信息, 转为 <code>ESRI</code> 的 <code>Geometry</code> 对象</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>查询经纬度信息, 获取其所在的区域, 有如下步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>遍历 <code>Geometry</code> 数组, 搜索经纬度所表示的 <code>Point</code> 对象在哪个区域内</p>
</li>
<li>
<p>返回区域的名称</p>
<div class="ulist">
<ul>
<li>
<p>使用 <code>UDF</code> 的目的是为了统计数据集, 后续会通过函数直接完成功能</p>
</li>
</ul>
</div>
</li>
</ol>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_7_会话统计">7. 会话统计</h2>
<div class="sectionbody">
<div class="dlist">
<dl>
<dt class="hdlist1">目标和步骤</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="dlist">
<dl>
<dt class="hdlist1">目标</dt>
<dd>
<div class="ulist">
<ul>
<li>
<p>统计每个行政区的所有行程, 查看每个行政区平均等候客人的时间</p>
</li>
<li>
<p>掌握会话统计的方式方法</p>
</li>
</ul>
</div>
</dd>
<dt class="hdlist1">步骤</dt>
<dd>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>会话统计的概念</p>
</li>
<li>
<p>功能实现</p>
</li>
</ol>
</div>
</dd>
</dl>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">会话统计的概念</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>需求分析</p>
<div class="exampleblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>需求</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>统计每个行政区的平均等客时间</p>
</div>
</div>
</div>
</li>
<li>
<p>需求可以拆分为如下几个步骤</p>
<div class="openblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>按照行政区分组</p>
</li>
<li>
<p>在每一个行政区中, 找到同一个出租车司机的先后两次订单, 本质就是再次针对司机的证件号再次分组</p>
</li>
<li>
<p>求出这两次订单的下车时间和上车时间只差, 便是等待客人的时间</p>
</li>
<li>
<p>针对一个行政区, 求得这个时间的平均数</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>问题: 分组效率太低</p>
<div class="openblock">
<div class="content">
<div class="paragraph">
<p>分组的效率相对较低</p>
</div>
<div class="ulist">
<ul>
<li>
<p>分组是 <code>Shuffle</code></p>
</li>
<li>
<p>两次分组, 包括后续的计算, 相对比较复杂</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>解决方案: 分区后在分区中排序</p>
<div class="openblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>按照 <code>License</code> 重新分区, 如此一来, 所有相同的司机的数据就会在同一个分区中</p>
</li>
<li>
<p>计算分区中连续两条数据的时间差</p>
<div class="imageblock">
<div class="content">
<img src="https://doc-1256053707.cos.ap-beijing.myqcloud.com/20190813003239.png" alt="20190813003239" width="800">
</div>
</div>
</li>
</ol>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
上述的计算存在一个问题, 一个分组会有多个司机的数据, 如何划分每个司机的数据边界? 其实可以先过滤一下, 计算时只保留同一个司机的数据
</td>
</tr>
</table>
</div>
</div>
</div>
</li>
<li>
<p>无论是刚才的多次分组, 还是后续的分区, 都是要找到每个司机的会话, 通过会话来完成功能, 也叫做会话分析</p>
</li>
</ul>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">功能实现</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>步骤</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>过滤掉没有经纬度的数据</p>
</li>
<li>
<p>按照 <code>License</code> 重新分区并按照 <code>License</code> 和 <code>pickUpTime</code> 排序</p>
</li>
<li>
<p>求得每个司机的下车和下次上车的时间差</p>
</li>
<li>
<p>求得每个行政区得统计数据</p>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>Step 1: 过滤没有经纬度的数据</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val taxiDone = taxiClean.where("dropOffX != 0 and dropOffY != 0 and pickUpX != 0 and pickUpY != 0")</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 2: 划分会话</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">val sessions = taxiDone.repartition('license)
  .sortWithinPartitions('license, 'pickUpTime)</code></pre>
</div>
</div>
</div>
</div>
</li>
<li>
<p>Step 3: 求得时间差</p>
<div class="exampleblock">
<div class="content">
<div class="olist arabic">
<ol class="arabic">
<li>
<p>处理每个分区, 通过 <code>Scala</code> 的 <code>API</code> 找到相邻的数据</p>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">sessions.mapPartitions(trips =&gt; {
  val viter = trips.sliding(2)
})</code></pre>
</div>
</div>
</li>
<li>
<p>过滤司机不同的相邻数据</p>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">sessions.mapPartitions(trips =&gt; {
  val viter = trips.sliding(2)
    .filter(_.size == 2)
    .filter(p =&gt; p.head.license == p.last.license)
})</code></pre>
</div>
</div>
</li>
<li>
<p>求得时间差</p>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">def boroughDuration(t1: Trip, t2: Trip): (String, Long) = {
  val borough = boroughLookUp(t1.dropOffX, t1.dropOffY)
  val duration = (t2.pickUpTime - t1.dropOffTime) / 1000
  (borough, duration)
}

val boroughDurations = sessions.mapPartitions(trips =&gt; {
  val viter = trips.sliding(2)
    .filter(_.size == 2)
    .filter(p =&gt; p.head.license == p.last.license)
  viter.map(p =&gt; boroughDuration(p.head, p.last))
}).toDF("borough", "seconds")</code></pre>
</div>
</div>
</li>
</ol>
</div>
</div>
</div>
</li>
<li>
<p>Step 4: 统计数据</p>
<div class="exampleblock">
<div class="content">
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code data-lang="scala" class="language-scala hljs">boroughDurations.where("seconds &gt; 0")
  .groupBy("borough")
  .agg(avg("seconds"), stddev("seconds"))
  .show()</code></pre>
</div>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
<dt class="hdlist1">总结</dt>
<dd>
<div class="sidebarblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>其实会话分析的难点就是理解需求</p>
<div class="openblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>需求是找到每个行政区的待客时间, 就是按照行政区分组</p>
</li>
<li>
<p>需求是找到待客时间, 就是按照司机进行分组, 并且还要按照时间进行排序, 才可找到一个司机相邻的两条数据</p>
</li>
</ul>
</div>
</div>
</div>
</li>
<li>
<p>但是分组和统计的效率较低</p>
<div class="openblock">
<div class="content">
<div class="ulist">
<ul>
<li>
<p>可以把相同司机的所有形成发往一个分区</p>
</li>
<li>
<p>然后按照司机的 <code>License</code> 和上车时间综合排序</p>
</li>
<li>
<p>这样就可以找到同一个司机的两次行程之间的差值</p>
</li>
</ul>
</div>
</div>
</div>
</li>
</ul>
</div>
</div>
</div>
</dd>
</dl>
</div>
</div>
</div>
</div>
<div id="footer">
<div id="footer-text">
Last updated 2019-08-13 01:13:53 +0800
</div>
</div>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.15.6/styles/github.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.15.6/highlight.min.js"></script>
<script>hljs.initHighlighting()</script>
</body>
</html>